BookmarkSubscribeRSS Feed
SAS_ter
Calcite | Level 5

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.

5 REPLIES 5
LinusH
Tourmaline | Level 20

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
SAS_ter
Calcite | Level 5

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

I will update the errors.

Thanks,

ballardw
Super User

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.

SAS_ter
Calcite | Level 5

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,

ballardw
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1128 views
  • 0 likes
  • 3 in conversation