topic Re: Splitting a file in to three based on record for particular key in SAS Programming

Splitting a file in to three based on record for particular key

MB123 — Sun, 18 Jun 2017 17:11:42 GMT

HI All

Need some help in particular requirement, I am very new SAS user.

I have data like this

value key

abc 1

bed 2

daf 1

trewr 1

fdsfds 1

fdsfsd 2

eqre 2

fdsfds 3

dsfds 3

I need to split the file into 3 files- if the key has more or equal to 4 rows then one file, if the key has 2-3 rows then one file, if key has less than 2 rows then another file.

Key values can be anything. I want to know how can I count the rows for each unique value of the key field and split the file.

Re: Splitting a file in to three based on record for particular key

kiranv_ — Sun, 18 Jun 2017 17:29:35 GMT

something like this in proc sql

proc sql;
create table want1 as
select * from have
group by key
having count(Key) ge 4;
create table want2 as
select * from have
group by key
having count(Key) between 2 and 3;
create table want3 as
select * from have
group by key
having count(Key) =  1  ;
quit;

Re: Splitting a file in to three based on record for particular key

PeterClemmensen — Sun, 18 Jun 2017 18:08:00 GMT

data have;
input value$ key;
datalines;
abc    1
bed    2
daf    1
trewr  1
fdsfds 1
fdsfsd 2
eqre   2
fdsfds 3
dsfds  3
;

proc sort data = have;
   by key;
run;

proc freq data = have noprint;
   tables key / nocum out = freqtable(drop = percent);
run;

data one twothree fourmore;
   merge have freqtable;
   by key;
   if count = 1 then output one;
   else if count in (2,3) then output twothree;
   else output fourmore; 
   drop count;
run;