Hi All..
I am new in SAS and currently need a solution for my problem .. Here, I will explain about my issue with my sample data and process, kindly need your help.
I have list of name :
Name_without_space |
DWIEYUDOPRIHANTO |
STANISURYANTO |
SHERLYRITANANSY |
HERERAFENAROSSA |
I want to make a specific table for each name and then finally compile them in to one data set.
So I wrote a macro like this:
OPTIONS MPRINT;
proc sql ;
select distinct name_without_space into : name_list separated by ' ' from MyData
;
quit;
%macro dummy;
%do i=0 %to %sysfunc(countw(&name_list));
%let next_name = %scan(&name_list, &i);
%let value = %str(&next_name);
%let code = %substr(&value,1,5);
proc sql;
create table cek_data_&code as
select *
from MyData
where name_without_space = "&value" ;
%end;
quit;
proc sql;
create table compile_all as
%do i=0 %to %sysfunc(countw(&name_list));
%let next_name = %scan(&name_list, &i);
%let value = %str(&next_name);
%let code = %substr(&value,1,5);
%if i = %sysfunc(countw(&name_list)) %then %do;
select *
from cek_data_&code
%end;
%else %do; /*%until (i = %sysfunc(countw(&name_list)));*/
select *
from cek_data_&code
outer union corr
%end;
%end;
;
quit;
%mend;
%dummy;
It works until make a specific table for each list name, but then I got error when try to create table compile_all .
The errors :
ERROR 22-322: Syntax error, expecting one of the following: (, SELECT.
Seems the error caused by the last part of query still get the "outer union corr" like this : select * from cek_data_HERER outer union corr ;
Kindly need help, so I can compile all data sets into one datasets. Thanks in advance.
So pull data out of CEK_DATA_&code into separate data sets, based on the value in &next_name, and then combine all these data sets into one?
No macros needed here. Do it all without macros. Do it all with one data pull.
UNTESTED CODE
proc sql ;
select distinct cats('cek_data_',substr(name_without_space,1,5)) into :name_list separated by ' ' from MyData;
quit;
data compile_all;
set &name_list;
run;
To better understand what your macro does, add
options mlogic mprint symbolgen;
to your code.
Thanks, Done that before, what I found for my problem is related to this part of macro :
%else %do;
select *
from cek_data_&code
outer union corr
%end;
it seems that even for the last data set , still it call OUTER UNION CORR caused by the loop condition , so the ended query like below :
select * from cek_data_SHERL outer union corr
select * from cek_data_HERER outer union corr ;
It should be no "outer union corr" for the last dataset . Do yo have any idea for this ?
@Moonlight_26 wrote:
Thanks, Done that before, what I found for my problem is related to this part of macro :
%else %do;
select *
from cek_data_&code
outer union corr
%end;
it seems that even for the last data set , still it call OUTER UNION CORR caused by the loop condition , so the ended query like below :
select * from cek_data_SHERL outer union corr
select * from cek_data_HERER outer union corr ;
It should be no "outer union corr" for the last dataset . Do yo have any idea for this ?
Don't generate the extra one.
You could add another %IF statement to determine whether you need that string generated or not.
But I find it much easier to just use a macro variable for the separator characters. Then I start with it empty and then set it after the first time the loop.
Here is the idea:
%macro test(string);
%local i sep;
%let sep=;
%do i=1 %to %sysfunc(countw(&string,|));
&sep. %scan(&string,&i,|)
%let sep=outer union corr;
%end;
%mend;
Results:
1273 %macro test(string); 1274 %Local i sep; 1275 %let sep=; 1276 %do i=1 %to %sysfunc(countw(&string,|)); 1277 &sep. %scan(&string,&i,|) 1278 %let sep=outer union corr; 1279 %end; 1280 %mend; 1281 %put "%test(abc|def|xyz)"; " abc outer union corr def outer union corr xyz"
So pull data out of CEK_DATA_&code into separate data sets, based on the value in &next_name, and then combine all these data sets into one?
No macros needed here. Do it all without macros. Do it all with one data pull.
UNTESTED CODE
proc sql ;
select distinct cats('cek_data_',substr(name_without_space,1,5)) into :name_list separated by ' ' from MyData;
quit;
data compile_all;
set &name_list;
run;
Can you generate the SQL query you are trying to use the macro to create by hand and get it to work?
Can you share such a query and explain what parts of it need to change?
I don't understand why you would extract in pieces and then put the pieces back together.
Why not just extract all at once?
data want;
set
cek_data_DWIEY
cek_data_STANI
cek_data_SHERL
cek_data_HERER
;
run;
I fail to understand why you would first split your single table into multiple tables only to combine them again into a single table. This is most of the time a sub-optimal approach.
Because I'm apparently missing something below just some "food for thought".
1. Create some sample data
data MyData;
infile datalines truncover;
input name_without_space $50.;
datalines;
cek_data_DWIEY
cek_data_STANI
cek_data_STANI
cek_data_STANI
cek_data_SHERL
cek_data_HERER
;
2. Create list of distinct values for use with an IN operator.
%let selection=;
proc sql noprint;
select distinct cats("'",name_without_space,"'") into :selection separated by ','
from MyData
;
quit;
%put %nrbquote(&selection);
data want;
set mydata(where=(name_without_space in (&selection)));
run;
Or here for your problem to create a select * <table> union all corr
%let combine=;
proc sql noprint;
select distinct catx(' ','select * from',name_without_space) into :combine separated by ' union all corr '
from MyData
;
quit;
%put %nrbquote(&combine);
From SAS log:
select * from cek_data_DWIEY union all corr select * from cek_data_HERER union all corr select * from cek_data_SHERL union all corr select * from cek_data_STANI
And last but not least: SAS macro language complicates code and should only be used if you need something dynamic that you can't achieve with "normal" SAS language. It's a rather common beginners mistake to use macro language too quickly. If you explain us a bit more in detail what you have and what you need then it's likely someone can suggest an approach that doesn't require SAS macro code at all.
You already have the answer but just for fun SAS Packages approach with MacroArray package:
data have;
input Name_without_space $32.;
cards;
DWIEYUDOPRIHANTO
STANISURYANTO
SHERLYRITANANSY
HERERAFENAROSSA
;
run;
filename packages "/path/to/my/packages";
%include packages(SPFinit.sas);
%loadPackage(macroArray)
/*
%helpPackage(macroarray,'%array()')
*/
data have2;
set have;
length short_names $ 14;
short_names = 'cek_data_' !! Name_without_space;
run;
%array(ds = have2, vars = short_names|, macarray=Y)
/* %put %do_over(short_names) ; */
data want;
set %do_over(short_names) ;
run;
Bart
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.