topic split a data based on different value in a variable. in SAS Programming

split a data based on different value in a variable.

France — Fri, 21 Sep 2018 11:53:28 GMT

dear all,

how can I split a data based on the different values in a variable?

for example, I have a data

person_ctry_code, company_name
US,A
UK,B
GB,C
NZ,D

how could I split it into three datasets? one is

person_ctry_code, company_name
US,A

, second is

person_ctry_code, company_name
GB,B
GB,C

third is,

person_ctry_code, company_name
NZ,D

thanks in advance.

Re: split a data based on different value in a variable.

RW9 — Fri, 21 Sep 2018 12:12:09 GMT

You would not want to. This would mean 3 * storage needs (e.g. 3 * header blocks, 3 * read, 3 * write etc.), not to mention you would then need masses of complicated messy code which will fall over each time. Never split data up unless there is an extremely good reason (i.e. if there is a restriction on the recipient end, or data is huge > tb).

As for how, you would:

proc sort data=have out=loop nodupkey;
  by company_name;
run;
data _null_:
  set loop;
  call execute(cat('data ',strip(company_name),'; set have; where company_name="',strip(company_name),'"; run;'));
run;

Re: split a data based on different value in a variable.

PeterClemmensen — Fri, 21 Sep 2018 12:16:54 GMT

First: Don't do this. Usually it is a bad idea to split up a data set like this. Use By Group processing instead.

Secondly, there are several ways to do this. Here is a hash object approach.

data have;
input person_ctry_code $ company_name $;
infile datalines dlm=",";
datalines;
US,A
GB,B
GB,C
NZ,D
;

proc sort data = have out=SortHave;
   by person_ctry_code;
run;
 
data _null_;
   if _n_=1 then do;
      if 0 then set SortHave;
      declare hash h(dataset:"SortHave(obs=0)", multidata:'y');
      h.definekey(all:'y');
      h.definedata(all:'y');
      h.definedone();
   end;
   do until(last.person_ctry_code);                                     
      set SortHave;
      by person_ctry_code;
      h.add();
   end;
   h.output(dataset:person_ctry_code);
   h.clear();
run;