topic Re: Split Dataset Based on Empty Records in SAS Programming

Split Dataset Based on Empty Records

My_SAS — Mon, 14 May 2012 10:07:53 GMT

I am ahving dataset i should split it in to differnt datasets based if the record is empty
i dont know how many such records are there

data l;
input name $ 1-3 age 5-12;
cards;
q 1
(EMPTY RECORDS)
e 3
(EMPTY RECORDS)
r 7
(EMPTY RECORDS)
w 6
run;

output

Dataset l1:

Name age
q 1

Dataset l2:
Name age
e 3

Dataset l3:
Name age
r 7

Dataset l4:
Name age
w 6

You can Refer the Txt file also

Re: Split Dataset Based on Empty Records

Linlin — Mon, 14 May 2012 12:54:20 GMT

data class;

set sashelp.class(keep=name age);

if _n_ in (2,4,6,8,12,14,16) then call missing(name,age);

data temp;

set class end=last;

retain count 1;

n_miss=cmiss(name,age);

count=ifn(n_miss=2,count+1,count);

if last then call symputx('total',count);

run;

options mprint;

%macro test;

data %do i=1 %to &total; want&i %end;;

set temp;

%do i=1 %to &total;

if count=&i and n_miss=0 then output want&i;

%end;

keep name age;

run;

%mend;

%test

Linlin

Re: Split Dataset Based on Empty Records

Ksharp — Tue, 15 May 2012 04:40:35 GMT

Did you mean every single dataset has the same name and the same age ?

data l;
input name $  age;
cards;
q    1
.    . 
e    3
.    .  
r    7
.    .  
w    6
;
run;
data _null_;
if _n_ eq 1 then do;
 if 0 then set l ;
 declare hash ha1(ordered:'a');
 declare hiter hi1('ha1');
 declare hash ha2;
  ha1.definekey('name','age');
  ha1.definedata('ha2');
  ha1.definedone();
end;
set l end=last;
if ha1.find()=0 then ha2.add();
 else do;
        ha2=_new_ hash(multidata:'y');
         ha2.definekey('name','age');
         ha2.definedata('name','age');
         ha2.definedone();
         ha2.add();
         ha1.add();
       end;
if last then do; n=-1;
               do while(hi1.next()=0);
                n+1;
                if n ne 0 then ha2.output(dataset: cats('l',n));
               end;
             end;
run;

Ksharp

Re: Split Dataset Based on Empty Records

sas_Forum — Tue, 15 May 2012 06:23:21 GMT

data l;
input name $ 1-2 age$ 3-4 no $ 5-6 id $ 7-9;
cards;
q
q 1 1 1

e
e 3 3 3

r
r 7 7 7

w
w 6 6 6
run;
proc print;
run;

data temp;

set l end=last;

retain count 1;

n_miss=cmiss(name,age);

count=ifn(n_miss=2,count+1,count);

if last then call symputx('total',count);

run;

options mprint;

%macro test;

data %do i=1 %to &total; want&i %end;;

set temp;

%do i=1 %to &total;

if count=&i and n_miss=0 then output want&i;

%end;

run;

%mend;

%test;

proc print data=want1;
run;

Actually my data is like this i have tryed your code

But out put should be

output

Dataset l1:

Name age no id
q
q 1 1 1

Dataset l2:
Name age
e
e 3 3 3

Dataset l3:
r
Name age
r 7 7 7

Dataset l4:
Name age
w
w 6 6 6

But it was coming only one row can u help me in this

Re: Split Dataset Based on Empty Records

shivas — Tue, 15 May 2012 09:41:21 GMT

Hi,

Try this..

data ss;

input name $ 1-2 age$ 3-4 no $ 5-6 id $ 7-9;

cards;

q 1 1 1

e 3 3 3

r 7 7 7

w 6 6 6

run;

proc sql;

select distinct name into :name1-:name4 from ss where name ne '';

quit;

%put &name1;

data &name1 &name2 &name3 &name4;

set ss;

if name="&name1" then output &name1;

if name="&name2" then output &name2;

if name="&name3" then output &name3;

if name="&name4" then output &name4;

run;

or you can loop it with macro.

Thanks,

Shiva

Re: Split Dataset Based on Empty Records

Linlin — Tue, 15 May 2012 11:15:11 GMT

try this one:

data have;

input name $ 1-2 age$ 3-4 no $ 5-6 id $ 7-9;

cards;

q 1 1 1

e 3 3 3

r 7 7 7

w 6 6 6

data temp;

set have end=last;

retain count 1;

n_miss=cmiss(name,age,no,id);

count=ifn(n_miss=4,count+1,count);

if last then call symputx('total',count-1);

run;

options mprint;

%macro test;

data %do i=1 %to &total; want&i %end;;

set temp;

%do i=1 %to &total;

if count=&i and n_miss<4 then output want&i;

%end;

drop n_miss count;

run;

%mend;

%test

Re: Split Dataset Based on Empty Records

Haikuo — Tue, 15 May 2012 18:34:32 GMT

Another Hash() approach:

data l;

input name $ age ;

cards;

q 1

. .

e 3

. .

r 7

. .

w 6

;

run;

proc sql;

select count(name) into :name from dictionary.columns where libname='WORK' AND MEMNAME='L';

%PUT &NAME;

data _null_;

if _n_=1 then do;

set l(obs=1);

dcl hash h(multidata:'y');

h.definekey('name', 'age');

h.definedata('name', 'age');

h.definedone();

end;

do until (cmiss(of _all_)<&NAME);

set l;

if cmiss(of _all_)<&NAME then h.add();

end;

n+1;

h.output(dataset:'test'||strip(put(n,best.)));

h.clear();

run;

Haikuo