topic Re: create new data set by looping over strings and compile the result into one data set in SAS Programming

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

Moonlight_26 — Tue, 05 Sep 2023 11:58:50 GMT

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.

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

LinusH — Tue, 05 Sep 2023 12:34:17 GMT

To better understand what your macro does, add

options mlogic mprint symbolgen;

to your code.

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

PaigeMiller — Tue, 05 Sep 2023 14:01:21 GMT

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;

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

Tom — Tue, 05 Sep 2023 13:42:03 GMT

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;

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

Moonlight_26 — Wed, 06 Sep 2023 03:23:22 GMT

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 ?

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

Tom — Wed, 06 Sep 2023 03:49:29 GMT

@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"

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

Patrick — Wed, 06 Sep 2023 05:10:54 GMT

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.

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

Moonlight_26 — Wed, 20 Sep 2023 08:02:03 GMT

Thank You,
My problem solved.

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

Moonlight_26 — Wed, 20 Sep 2023 08:04:44 GMT

Hi..

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

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

yabwon — Wed, 20 Sep 2023 08:42:11 GMT

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