[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 668
  • Last Modified:

SAS

I am trying to do the following:  
  Sort "Data5" dataset no duplicate keys on ‘Var3’, output to "TempData" temporary dataset (keep only ‘Var3’).
   For each variable ‘Number’ (this variable has numbers present from 2 to 18, also number 23 and 34) in "File1.dat" (except number 34), merge "TempData" with "Data3_<number>" using variable ‘VarID’ in "Data3_<number>" = ‘Var3’(11:19) in "TempData". One "Data3_<number>" to 0, 1 "TempData" (one to one match or one to none). I am trying to keep only matching "TempData" records.
   As my output, I need to output only a single “Data6” SAS dataset and ASCII file “Data6.dat” containing the "TempData" records for all numbers.

Please check the following code for errors:
proc sort data=dir.Data5 out=TempData (keep=Var3) nodupkey;
    by Var3;
run;

%macro first (nbr);
data dir.Data6;
              if (&nbr ^ne 34) then do;  
                  merge  TempData(in=a) dir.Data3&nbr (in=b);
                     by VarID;
                   if a then output;
              end;
             
    data _null_;
       set dir.Data6;
         file dir.Data6.dat;
              put @1  VarID   $char9.
                    @10 Var2   $char2.
                    @12 Var7   $char1.
                    @13 Var10 $char3.;
    run;
%mend first;

data _null_;
     infile “dir.File1.dat”;  
       input @1 Number $char2.;
         call symput ('nbr', number);
         call execute ('%first(&nbr)');
run;

Please Note: I do not need to check for "Data3_<number>" datasets existence, - all datasets (from 2 to 18, also number 23 and 34) are already present in the "dir" directory.

After running above code through SAS compiler, I can see that my merges are not working, so my question is:
How can I properly merge "TempData" with "Data3_<number>" using variable ‘VarID’ in "Data3_<number>" = ‘Var3’(11:19) in "TempData" ?

Last Note:
'VarID' is only present in the "Data3_<number>" dataset
‘Var3’(11:19) is only present in the "TempData" dataset
0
labradorchik
Asked:
labradorchik
  • 2
  • 2
1 Solution
 
theartfuldazzlerCommented:
Hi

I think your issue might be the use of the IF statement rather than the macro %IF statement,

In general, I am not the biggest fan of the SAS merge statement, as it can give unexpected results due to the way it does the merge.  I tend to prefer SQL JOINS as they give more logical results.

I would re-write your macro in the following form:

 
%macro first (nbr);
  %if (&nbr ~= 34) %then %do;  
      PROC SQL;
          Create table Dir.Data6 as
		      select b.* from TempData as a
				inner join dir.Data3&nbr as b
                     on a.VarID = b.VarID;
   %end;
             
    data _null_;
       set dir.Data6;
         file "dir.Data6.dat";
              put @1  VarID   $char9.
                    @10 Var2   $char2.
                    @12 Var7   $char1.
                    @13 Var10 $char3.;
    run;
%mend first;

Open in new window


Reading thru your requirements, I get the suspicion that you want to append all the data into both the SAS data set DATA6 and text file Data6.dat.

To do this, you would need to add the MOD statememt to FILE statement (this will append the data to your data6.dat file - and I would add a FDELETE statemenmt at the beginning to make sure that you started with a new DAT file everytime.

To append the data into one SAS dataset, you would need to either use a PROC APPEND, or I would use a PROC SQL INSERT statement:

 
DATA _NULL_;
  IF FEXIST("dir.Data6.dat") THEN rc= FDELETE("dir.Data6.dat");
RUN;

PROC SQL;
  drop table dir.Data6;
QUIT;

%macro first (nbr);
  %if (&nbr ~= 34) %then %do;  
      PROC SQL;
          Create table Part_of_Data6 as
		      select b.* from TempData as a
				inner join dir.Data3&nbr as b
                     on a.VarID = b.VarID;

		 %IF NOT %SYSFUNC(EXIST(Dir.Data6)) %THEN %DO;
		 	PROC SQL;
			   Create table Dir.data6 as
			      select * from Part_of_Data6;
		%END;
		%ELSE %DO;
		 	PROC SQL;
			   Insert into Dir.data6
			      select * from Part_of_Data6;
		%END;
             
	    data _null_;
	       set dir.Data6;
	         file "dir.Data6.dat" MOD;
	              put @1  VarID   $char9.
	                  @10 Var2   $char2.
	                  @12 Var7   $char1.
	                  @13 Var10 $char3.;
	    run;
   %end;
%mend first;

Open in new window

0
 
labradorchikAuthor Commented:
Thank you very much for your comments!!
Yes, you are correct,  I want to append all the data into both the SAS data set DATA6 and ASCII file Data6.dat.

Let me rewrite full code based on the requirements and also ask you some questions so I get better idea what I am doing while merging those two datasets based on those two variables.

PROC  SORT data=dir.Data5 out=TempData (keep=Var3) nodupkey;
    by Var3;
RUN;

DATA _NULL_;
IF FEXIST("dir.Data6.dat") THEN rc= FDELETE("dir.Data6.dat");
RUN;

PROC SQL;
  drop table dir.Data6;
QUIT;

%macro first (nbr);
  %if (&nbr ~= 34) %then %do;  
      PROC SQL;
          Create table Part_of_Data6 as
		      select b.* from TempData as a
				inner join dir.Data3_&nbr as b
                     on a.VarID = b.VarID;

		 %IF NOT %SYSFUNC(EXIST(dir.Data6)) %THEN %DO;
		 	PROC SQL;
			   Create table Dir.data6 as
			      select * from Part_of_Data6;
		%END;
		%ELSE %DO;
		 	PROC SQL;
			   Insert into Dir.data6
			      select * from Part_of_Data6;
		%END;
             
	    DATA _NULL_;
	       set dir.Data6;
	         file "dir.Data6.dat" MOD;
	              put @1  VarID   $char9.
	                    @10 Var2   $char2.
	                    @12 Var7   $char1.
	                    @13 Var10 $char3.;
	    RUN;
   %END;
%mend first;

DATA _NULL_;
     infile “dir.File1.dat”;  
       INPUT @1 Number $char2.;
         call symput ('nbr', number);
         call execute ('%first(&nbr)');
RUN;

Open in new window


QUESTION:
Does your PROC SQL code below accomplish the same task as it says in the requirements?

 PROC SQL;
          Create table Part_of_Data6 as
                  select b.* from TempData as a
                        inner join dir.Data3_&nbr as b
                     on a.VarID = b.VarID;

Requirements were:
Merge "TempData" with "Data3_<number>" using variable ‘VarID’ in "Data3_<number>" = ‘Var3’(11:19) in "TempData". One "Data3_<number>" to 0, 1 "TempData" (one to one match or one to none match). Keep only matching "TempData" records.

Please note:  ‘VarID’ in "Data3_<number>" = ‘Var3’(11:19) in "TempData".
0
 
theartfuldazzlerCommented:
Hi

Yes it does. Except I'm not sure what you mean by (11:19)?
0
 
labradorchikAuthor Commented:
Thank you again for your comments!!  I found a way how to merge both datasets using MERGE instead of proc SQL, - I just felt more comfortable with using MERGE.  :)
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now