Create multiple datasets from 1 dataset based on a variable in the dataset

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Create multiple datasets from 1 dataset based on a variable in the dataset

Hi

I have dataset1 with the following structure :

BookVar         var1     var2     var3     var4

Widget1          a          b          c          d

Widget1          b          c          d          e

Widget2          a          d          r          g

Widget2          d          u          g          a   

I need to create datasets Widget1 to Widget2, with the same variables as above. 

The number of different types of Widgets varies, so I store this in a macro variable called &n.  In this case &n equates to 2, as there are two different types of Widget in the dataset

I have also stored the names for the different types of Widgets into macro variables &T1 to &T2

&T1=Widget1

&T2=Widget2

I now need to write a macro loop to split dataset1 into sets Widget1 and Widget2, but I am not getting it right.  The following code doesn't work, and I can see why it doesn't work, but I can't fix it.  However, it gives you an idea of what I am trying to achieve :

%do i = 1 %to &n;
     data lib.&T1;
         set lib.dataset1;
         where BookVar = &T1;
     run;
%end;

Please, any suggestions ?


Accepted Solutions
Solution
‎07-25-2014 07:28 AM
Contributor
Posts: 40

Re: Create multiple datasets from 1 dataset based on a variable in the dataset

Thanks Jaap and RW9, that was useful input.  I found a way to create the datasets and write them to text files in one step as follows :

%macro SplitDataset;

  proc sql noprint;

    select count(distinct(BookVar2)) into :n

    from lib.PS3;

  quit;

  %let n = &n;

  proc sql noprint;

  select distinct BookVar2

    into :t1 - :t&n

    from lib.PS3;

  quit;

  %put &t50;

%do i = 1 %to &n;

PROC EXPORT DATA = lib.PS3(where=(BookVar2="&&t&i"))

            OUTFILE="&ProjPath\PS_&&TheDate &&t&i...txt"

            DBMS= DLM

            REPLACE;

     DELIMITER='09'x;

     PUTNAMES=YES;

RUN;

%END;

%mend SplitDataset;

%SplitDataset

View solution in original post


All Replies
Valued Guide
Posts: 3,206

Re: Create multiple datasets from 1 dataset based on a variable in the dataset

https://communities.sas.com/message/168503 google on splitting datasets....

---->-- ja karman --<-----
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Create multiple datasets from 1 dataset based on a variable in the dataset

data widget1 widget2;

     set have;

     if bookvar="Widget1" then output widget1;

     if ...

run;

Or automatically:

proc sql;

     create table LOOP as

     select distinct bookvar

     from have;

quit;

data _null_;

     set loop;

     call execute('data '||strip(bookvar)||'; set have; where bookvar="'||strip(bookvar)||'"; run;');

run;

Solution
‎07-25-2014 07:28 AM
Contributor
Posts: 40

Re: Create multiple datasets from 1 dataset based on a variable in the dataset

Thanks Jaap and RW9, that was useful input.  I found a way to create the datasets and write them to text files in one step as follows :

%macro SplitDataset;

  proc sql noprint;

    select count(distinct(BookVar2)) into :n

    from lib.PS3;

  quit;

  %let n = &n;

  proc sql noprint;

  select distinct BookVar2

    into :t1 - :t&n

    from lib.PS3;

  quit;

  %put &t50;

%do i = 1 %to &n;

PROC EXPORT DATA = lib.PS3(where=(BookVar2="&&t&i"))

            OUTFILE="&ProjPath\PS_&&TheDate &&t&i...txt"

            DBMS= DLM

            REPLACE;

     DELIMITER='09'x;

     PUTNAMES=YES;

RUN;

%END;

%mend SplitDataset;

%SplitDataset

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Create multiple datasets from 1 dataset based on a variable in the dataset

just two things, 1 I would use a specific delimiter rather than tab as tab is not printable and leads to confusion with spaces.  Second, you can just do:

proc sql;

     create table LOOP as

     select     distinct     BOOKVAR

     from       HAVE;

quit;

data _null_;

     call execute('proc export data=lib.ps3 (where=bookvar="'||strip(bookvar)||'"))

                                             outfile="...\PS_%sysfunc(date(),date9.)_'||strip(put(_n_,best.))||'.csv" replace;

                               putnames=YES;

                          run;');

run;

Contributor
Posts: 40

Re: Create multiple datasets from 1 dataset based on a variable in the dataset

Thanks RW - much appreciated

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 1640 views
  • 6 likes
  • 3 in conversation