topic Re: SAS query - Split data to different dataset in SAS Programming

SAS query - Split data to different dataset

scb — Sun, 01 Nov 2020 02:03:18 GMT

May I know how to split dataset into diff dataset based on diff team? I could have 900 teams. Thanks.

DATA have;
INPUT ID $ Team $ Point;
DATALINES;
111 T1 100
112 T1 300
113 T1 600

111 T2 550
112 T2 770
113 T2 890

111 T3 1000
112 T3 3003
113 T3 6003
;
run;

Desired result

dataset1 name: T1

ID Team Point

111 T1 100
112 T1 300
113 T1 600

dataset2 name: T2

ID Team Point

111 T2 550
112 T2 770
113 T2 890

dataset3 name: T3

ID Team Point

111 T3 1000
112 T3 3003
113 T3 6003

Re: SAS query - Split data to different dataset

novinosrin — Sun, 01 Nov 2020 02:43:41 GMT


DATA have;
INPUT ID $ Team $ Point;
DATALINES;
111 T1 100
112 T1 300
113 T1 600
111 T2 550
112 T2 770
113 T2 890
111 T3 1000
112 T3 3003
113 T3 6003
;
run;

data _null_;
 if _n_=1 then do;
  dcl hash h(dataset:'have(obs=0)',multidata:'y',ordered:'y');
  h.definekey('id');
  h.definedata(all:'y');
  h.definedone();
 end;
 do until(last.team);
  set have;
  by team;
  h.add();
 end;
 h.output(dataset:team);
 h.clear();
run;

Re: SAS query - Split data to different dataset

Ksharp — Sun, 01 Nov 2020 08:36:27 GMT

If your table was not sorted and not big.

DATA have;
INPUT ID $ Team $ Point;
DATALINES;
111 T1 100
112 T1 300
113 T1 600
111 T2 550
112 T2 770
113 T2 890
111 T3 1000
112 T3 3003
113 T3 6003
;
run;
proc freq data=have noprint;
table team/out=levels nopercent;
run;
data _null_;
 set levels;
 call execute(cat('data ',team,';set have;if team="',strip(team),'";run;'));
run;

Re: SAS query - Split data to different dataset

Kurt_Bremser — Sun, 01 Nov 2020 08:37:37 GMT

The most important question here: what for?

In 99% of cases, splitting a dataset makes further processing more complicated and less efficient. So my answer is:

DON'T DO IT!

Please provide more details if you think you can persuade me that splitting your dataset makes sense.

Re: SAS query - Split data to different dataset

Reeza — Mon, 02 Nov 2020 05:36:40 GMT

I can almost guarantee there's no good reason to do this in SAS...especially 900 times.