topic Re: SAS macro - looping through list and creating new dataset in SAS Programming

SAS macro - looping through list and creating new dataset

jrdykstr93 — Fri, 21 Sep 2018 03:11:05 GMT

Hello,

Right now, I have a data-set that looks like something along the following(lets call this data-set A):

var1	var2
x	2
x	5
a	7
a	2
x	7
b	8

What i am trying to do, is loop through var2 and export different datasets based on the value of var2. So in this example, i would create four data-sets, each out corresponding to the unique amount of values in var2(var2 is numeric). Right now, i grab the unique amount of values from a different data-set(lets call it B) and put them into a list with this code:

proc sql; select var2 into :list separated by ' ' from B; quit;

From there I am trying to run the following code to create the data-sets.

%macro splitting;
%do i=1 %to %sysfunc(countw(&list.));
	%let block&i. = %scan(&list., &i.,%str());
	%put block&i.
		proc sql;
		    create table table&i. as 
		    select *
		    from A
		    where var2 contains &block&i.;
		quit;
%end;
%mend splitting;

I could, go through and do many different if-then statements, but i would rather not considering how many different data-sets I need to create. Can someone shed some light on the situation. Thanks.

Re: SAS macro - looping through list and creating new dataset

novinosrin — Fri, 21 Sep 2018 03:29:39 GMT

https://blogs.sas.com/content/sasdummy/2015/01/26/how-to-split-one-data-set-into-many/

http://www.sascommunity.org/wiki/Split_Data_into_Subsets

Re: SAS macro - looping through list and creating new dataset

Reeza — Fri, 21 Sep 2018 03:53:50 GMT

The general consensus is to not do this, by group processing is efficient though there are a few procs that don’t support it. In those cases, it’s still usually easier to filter on the fly with a WHERE option.

Most people still disregard this and use an approach as outlined in the posts from novinosrin.

Re: SAS macro - looping through list and creating new dataset

jrdykstr93 — Fri, 21 Sep 2018 15:18:53 GMT

ok, sounds good. I guess why I am trying to do this is because I am trying to stack multiple occurrences of the same variable in the same by-group that i need to make the "ID" when i transpose, and then append the data-sets together I will probably post another question based on this, than rather looping through macros. Please take a look.

Re: SAS macro - looping through list and creating new dataset

jrdykstr93 — Fri, 21 Sep 2018 16:38:23 GMT

https://communities.sas.com/t5/SAS-Programming/Transposing-and-Stacking/m-p/497899

Link to new questions.