create new data set by looping over strings and compile the result into one data set

[Moonlight_26]

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. 

[LinusH]

To better understand what your macro does, add

options mlogic mprint symbolgen;

to your code.

[Moonlight_26]

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]

---

@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 · Accepted solution]

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;

[Moonlight_26]

Thank You,
My problem solved.

[Tom]

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]

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]

Hi..

Actually, I have another use case but to simplify capture my problem, I use this case.

[yabwon]

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