Help using Base SAS procedures

%Macro help for Proc Append

Reply
Occasional Contributor
Posts: 16

%Macro help for Proc Append

Hi,

I am trying to use the following macro to append two datasets that have the same variable names, but some of the character variables have different lengths in the two data sets:

49   %macro union(dsn1=,whcd dsn2=,whjv out=

49 ! workinghourscd_jv_combined);

ERROR: Expecting comma (to separate macro parameters) or close

       parenthesis (to end parameter list) but found: dsn2

ERROR: A dummy macro will be compiled.

50

51      proc contents data=&dsn1 noprint

52         out=out1;

53

54      proc contents data=&dsn2 noprint

55         out=out2;

56      run;

57

58      data _null_;

59         file "workinghourscd_jv_combined.sas";

60         merge out1 out2 end=last;

61         by ID;

62         if _n_ = 1 then put "Data &out;";

63         l = max(length,length2);

64         if last then do;

65            put "   set &dsn1 &dsn2;";

66            put "run;";

67         end;

68      run;

69

70      %include "workinghourscd_jv_combined.sas";

71

72   %mend union;

Please help.

Thanks,

N.

Super User
Posts: 5,430

Re: %Macro help for Proc Append

I don't see the macro call, so perhaps is that why it isn't executed...?

Impossible to help if you don't share the log with the errors. It seems you have more than one problem?

Data never sleeps
Occasional Contributor
Posts: 16

Re: %Macro help for Proc Append

sorry, isn't the %macro union(dsn1=workinghourscd, dsn2=workinghoursjv, out= workinghourscd_jv_combined); the macro call?

I will update the errors.

Thanks,

Super User
Posts: 11,343

Re: %Macro help for Proc Append

SAS'ter wrote:

sorry, isn't the %macro union(dsn1=workinghourscd, dsn2=workinghoursjv, out= workinghourscd_jv_combined); the macro call?

No, that is the start of definition. To excute a macro you would have something like

%union(dsn1=workinghourscd, dsn2=workinghoursjv, out= workinghourscd_jv_combined);

You're going to need a lot more work if your goal is to remove the warning about mismatched lengths. If all of the longer variables are in one dataset the quick and dirty approach would be to place that dataset name first on the SET statement and ignore the warning. Otherwise you will want to create a Length statement that appears beforethe Set statement.

Occasional Contributor
Posts: 16

Re: %Macro help for Proc Append

Thanks, I will remove the "macro" bit.

My goal is to automate it, so that SAS looks at the two data sets and chooses the longest length of the same variables to append.

Thanks,

Super User
Posts: 11,343

Re: %Macro help for Proc Append

Something like this will put the character variables into a list suitable for use in a length statement in a data step

The DSN1 and 2 must resolve to be in upper case, if they are in separate libraries than you need to modify the parameters to include both libraries, (lib1=, lib2= for example), make similar %upcase assignment, in the comparison (libname in ("&lib1","&lib2")) and the set statement, set &lib1..&dsn1 &lib2..&dsn2 ;

Note the value for the comparison TYPE='char' , char must be lower case as that is how this is stored in the dictionary table.

%macro stack (lib=, dsn1=, dsn2=, outset=);

%let lib=%upcase(&lib);

%let dsn1=%upcase(&dsn1);

%let dsn2=%upcase(&dsn2);

proc sql;

/*   create table varlengths as*/

   select string into :varlengths separated by ' '

   from (

   select catx(' $ ',name,MAX(length)) as string

   from dictionary.columns

   where libname="&LIB" and memname in ("&DSN1","&DSN2")

      AND TYPE='char'

   group by name)

     ;

QUIT;

data &outset;

     length &varlengths ;

     set &lib..&dsn1

           &lib..&dsn2;

run;

%mend;

and to execute somethingl like:

%stack (lib=work, dsn1=set1, dsn2=set2, outset=combinedset);

Ask a Question
Discussion stats
  • 5 replies
  • 338 views
  • 0 likes
  • 3 in conversation