DATA Step, Macro, Functions and more

How to split a dataset by each row

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

How to split a dataset by each row

Hello I have the following dataset format:

R1R2R3R4R5
ABCDE
FGHIJ
KLMNO
PQRST
...............

and I would like to create 4 new datasets photo1, photo2, photo3, photo4, etc:

R1R2R3R4R5
ABCDE

R1R2R3R4R5
FGHIJ

etc. I am not sure if this can be done using a BY statement some how or maybe through a proc append, etc. Any suggestions?


Accepted Solutions
Solution
‎08-12-2014 04:58 PM
Super User
Posts: 19,770

Re: How to split a dataset by each row

Here's an example that splits the CLASS file into one row per file.

It assumes that each file is for each name. Your exact code will vary by how you want to split the file.

data _null_;

    set sashelp.class;

    by name notsorted;

    if first.name then do;

        call execute("data row"||put(_n_,z2.)||"; set sashelp.class(where=(name='"||name||"'));run;");

    end;

run;

View solution in original post


All Replies
Super User
Posts: 19,770

Re: How to split a dataset by each row

There's many ways, but I have to ask why, there's almost always a better way to deal with this situation.

Split Data into Subsets - sasCommunity

Solution
‎08-12-2014 04:58 PM
Super User
Posts: 19,770

Re: How to split a dataset by each row

Here's an example that splits the CLASS file into one row per file.

It assumes that each file is for each name. Your exact code will vary by how you want to split the file.

data _null_;

    set sashelp.class;

    by name notsorted;

    if first.name then do;

        call execute("data row"||put(_n_,z2.)||"; set sashelp.class(where=(name='"||name||"'));run;");

    end;

run;

Trusted Advisor
Posts: 1,228

Re: How to split a dataset by each row

data have;
input R1 $ R2 $ R3 $ R4 $ R5 $;
CNT+1;
datalines;
A B C D E
F G H I J
K L M N O
P Q R S T
;

proc sql;
select max(cnt) into :cnt from have;
quit;
%macro sets;
data
%do i=1 %to &cnt;
photo&i
%end;
;

set have(drop=cnt);

if _n_=1 then output photo1;
%do i = 2 %to &cnt;
        else if _n_=&i then output photo&i;
%end;
run;
%mend sets;
%sets

Contributor
Posts: 39

Re: How to split a dataset by each row

Thank you both!

Super User
Posts: 10,018

Re: How to split a dataset by each row

The fastest way is using Hash Table.




data _null_;
 if _n_ eq 1 then do;
  if 0 then set sashelp.class;
  declare hash h();
   h.definekey('n');
   h.definedata('name','age','weight','height');
   h.definedone();
end;
set sashelp.class;
n+1;h.add();h.output(dataset: cats('_',n)); h.clear();
run;

Xia Keshan

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 335 views
  • 6 likes
  • 4 in conversation