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, #, +, @.
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;
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.
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
I think that the DATA _NULL_; step needs a modification, it should say:
put " length " CDNUMPOL " $ 12;";
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.