Combining Datasets on variable field length

[Sudhan]

Hi,

I am quite new to SAS. I am trying to combine to datasets of variable field Lengths.

Example: 

Table_A

IDENTIFIER| DC_001|DC_002|

------------------------------------------------

100| Sample_Text |(blank)|

Table_B

IDENTIFIER| DC_001|DC_002|

------------------------------------------------

500|Sam|Sample|

Output should be stored in a table like

Output_Table

IDENTIFIER| DC_001|DC_002|

------------------------------------------------

100| Sample_Text |(blank)|

500|Sam|Sample|

I am trying to use the following macro, but i have a error and not sure where I have gone wrong

%macro union(dsn1=,   /*Name of the first data set */
             dsn2=,   /*Name of the second data set */
             out=     /*Name of combined data set */);
proc contents data=&dsn1 noprint
   out=out1(keep=name type length where=(type=2));

proc contents data=&dsn2 noprint
   out=out2(keep=name type length where=(type=2));
run;

data _null_;
   file "combined.sas";
   merge out1 out2(rename=(length=length2)) end=last;
   by IDENTIFIER;
   if _n_ = 1 then put "Data &out;";
   l = max(length,length2);
   put " length " IDENTIFIER " $ "12";";
   if last then do;
      put " set &dsn1 &dsn2;";
      put "run;";
   end;
run;

%include "combined.sas";

%mend union;

%union(dsn1=TABLE_A,dsn2=TABLE_B,out=OUTPUT_Table);

ERROR 22-322: Syntax error, expecting one of the following: a name, #, +, @.

[PaigeMiller]

Include this line as the first line of your program, and then run it again.

options mprint;

Then, please follow these instructions carefully. Copy the LOG from this macro (the entire log of this macro step, code and errors and warnings, with nothing chopped out) as text and paste it into the window here at SAS communities after you click on the </> icon. DO NOT SKIP THIS STEP. This ensures that the LOG will be formatted properly and readable, which helps us (and helps you) determine the problem.

[Sudhan]

Thank you.

1                                                          The SAS System                                 11:07 Monday, June 8, 2020

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program (2)';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=ACTIVEX;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HtmlBlue
17             STYLESHEET=(URL="###############################################################################
17       ! 3F/Root/VFS/ProgramFilesX86/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         %macro union(dsn1=,   /*Name of the first data set */
27                      dsn2=,   /*Name of the second data set */
28                      out=     /*Name of combined data set */);
29         options mprint;
30         proc contents data=&dsn1 noprint
31            out=out1(keep=name type length where=(type=2));
32         
33         proc contents data=&dsn2 noprint
34            out=out2(keep=name type length where=(type=2));
35         run;
36         
37         data _null_;
38            file "combined.sas";
39            merge out1 out2(rename=(length=length2)) end=last;
40            by CDNUMPOL;
41            if _n_ = 1 then put "Data &out;";
42            l = max(length,length2);
43            put " length " CDNUMPOL " $ "12";";
                                      _____
                                      49
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

44            if last then do;
45               put " set &dsn1 &dsn2;";
46               put "run;";
47            end;
48         run;
49         
50         %include "combined.sas";
51         
2                                                          The SAS System                                 11:07 Monday, June 8, 2020

52         %mend union;
53         
54         %union(dsn1=MIG_TEST.TRANSFORMED_DATA,dsn2=TRANSFORMED_ARCHV,out=Out_file);
MPRINT(UNION):   options mprint;
MPRINT(UNION):   proc contents data=MIG_TEST.TRANSFORMED_DATA noprint out=out1(keep=name type length where=(type=2));
NOTE: The data set WORK.OUT1 has 334 observations and 3 variables.
NOTE: Compressing data set WORK.OUT1 increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MPRINT(UNION):   proc contents data=TRANSFORMED_ARCHV noprint out=out2(keep=name type length where=(type=2));
MPRINT(UNION):   run;

NOTE: The data set WORK.OUT2 has 335 observations and 3 variables.
NOTE: Compressing data set WORK.OUT2 increased size by 100.00 percent. 
      Compressed is 2 pages; un-compressed would require 1 pages.
NOTE: PROCEDURE CONTENTS used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

MPRINT(UNION):   data _null_;
MPRINT(UNION):   file "combined.sas";
MPRINT(UNION):   merge out1 out2(rename=(length=length2)) end=last;
MPRINT(UNION):   by CDNUMPOL;
NOTE: Line generated by the invoked macro "UNION".
54                                                                                put "Data &out;";    l = max(length,length2);
54       ! put " length " CDNUMPOL " $ "12";";    if last then do;       put " set &dsn1 &dsn2;";       put "run;";    end; run;
                                   _______
                                   49   22
54       ! %include
MPRINT(UNION):   if _n_ = 1 then put "Data Out_file;";
MPRINT(UNION):   l = max(length,length2);
MPRINT(UNION):   put " length " CDNUMPOL " $ "12";";
MPRINT(UNION):   if last then do;
MPRINT(UNION):   put " set MIG_TEST.TRANSFORMED_DATA TRANSFORMED_ARCHV;";
MPRINT(UNION):   put "run;";
MPRINT(UNION):   end;
MPRINT(UNION):   run;

NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release.  Inserting white space 
             between a quoted string and the succeeding identifier is recommended.

ERROR 22-322: Syntax error, expecting one of the following: a name, #, +, @.  

NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

WARNING: Physical file does not exist, /XXX/YYYY/sas/bin/SASConfigFolder/Lev1/SASXXXX/combined.sas.
ERROR: Cannot open %INCLUDE file combined.sas.
55         
56         GOPTIONS NOACCESSIBLE;
3                                                          The SAS System                                 11:07 Monday, June 8, 2020

57         %LET _CLIENTTASKLABEL=;
58         %LET _CLIENTPROCESSFLOWNAME=;
59         %LET _CLIENTPROJECTPATH=;
60         %LET _CLIENTPROJECTPATHHOST=;
61         %LET _CLIENTPROJECTNAME=;
62         %LET _SASPROGRAMFILE=;
63         %LET _SASPROGRAMFILEHOST=;
64         
65         ;*';*";*/;quit;run;
66         ODS _ALL_ CLOSE;
67         
68         
69         QUIT; RUN;
70         

[PaigeMiller]

I think that the DATA _NULL_; step needs a modification, it should say:

put " length " CDNUMPOL " $ 12;";

[Patrick]

If you are new to SAS then avoid macro coding. The macro you're trying to use also uses a rather complicated approach.

The easiest way to stack your tables and to ensure that the longest variable in any table "wins" is to use a SQL Union. Code like below will do what you want.

proc sql;
  create table want as
  select IDENTIFIER, DC_001, DC_002
  from table_a
  union all corr
  select IDENTIFIER, DC_001, DC_002
  from table_b
  ;
quit;

Another common way for such problems is to query the SAS dictionary tables (which have the metadata for your tables), populate the result into a SAS macro variable, and then use this macro variable in the next data step.

data have1;
  set sashelp.class;
run;

data have2;
  length sex $2;
  set sashelp.class;
  sex=cats('_',sex);
run;

/* query dictionary columns and populate macro variable &max_len
   with max. length of sex found in any of the source tables
*/
%let max_len=1;
proc sql noprint;
  select max(length) into :max_len trimmed
  from dictionary.columns
  where libname='WORK' and memname in ('HAVE1','HAVE2')
    and upcase(name)='SEX'
  ;
quit;
%put &=max_len;

/* define length of variable sex, then stack the source tables */
data want;
  length sex $&max_len;
  set have1 have2;
run;

proc print data=want;
run;

[Sudhan · Accepted solution]

I did something like this and it helped.

proc sql;
  create table Out_file as
    select *
    from Table_A
    outer union corr
    select *
    from Table_B
   ;
quit;

[Patrick]

Good that you've got your solution. 

And just as a tip: It's normally not appreciated if you mark your own post as solution.