BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Moonlight_26
Fluorite | Level 6

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. 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller

View solution in original post

9 REPLIES 9
LinusH
Tourmaline | Level 20

To better understand what your macro does, add

options mlogic mprint symbolgen;

to your code.

Data never sleeps
Moonlight_26
Fluorite | Level 6

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 ?

Tom
Super User Tom
Super User

@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"
PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
Tom
Super User Tom
Super User

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;
 
Patrick
Opal | Level 21

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.

 

Moonlight_26
Fluorite | Level 6
Hi..

Actually, I have another use case but to simplify capture my problem, I use this case.
yabwon
Onyx | Level 15

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



SAS Innovate 2025: Register Now

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!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 9 replies
  • 1474 views
  • 4 likes
  • 6 in conversation