topic Re: %Macro help for Proc Append in SAS Procedures

%Macro help for Proc Append

SAS_ter — Tue, 18 Feb 2014 14:56:04 GMT

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.

51 proc contents data=&dsn1 noprint

52 out=out1;

54 proc contents data=&dsn2 noprint

55 out=out2;

56 run;

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;

70 %include "workinghourscd_jv_combined.sas";

72 %mend union;

Please help.

Thanks,

Re: %Macro help for Proc Append

LinusH — Tue, 18 Feb 2014 15:28:15 GMT

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?

Re: %Macro help for Proc Append

SAS_ter — Tue, 18 Feb 2014 15:33:20 GMT

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

I will update the errors.

Thanks,

Re: %Macro help for Proc Append

ballardw — Tue, 18 Feb 2014 15:46:11 GMT

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.

Re: %Macro help for Proc Append

SAS_ter — Tue, 18 Feb 2014 16:08:26 GMT

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,

Re: %Macro help for Proc Append

ballardw — Tue, 18 Feb 2014 18:36:25 GMT

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);