DATA Step, Macro, Functions and more

Split Dataset Based on Empty Records

Reply
Contributor
Posts: 37

Split Dataset Based on Empty Records

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

Attachment
Super Contributor
Posts: 1,636

Re: Split Dataset Based on Empty Records

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

Frequent Contributor
Posts: 140

Re: Split Dataset Based on Empty Records

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

Super Contributor
Posts: 349

Re: Split Dataset Based on Empty Records

Posted in reply to sas_Forum

Hi,

Try this..

data ss;

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 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

Super Contributor
Posts: 1,636

Re: Split Dataset Based on Empty Records

Posted in reply to sas_Forum

try this one:

data have;

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

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

Super User
Posts: 10,046

Re: Split Dataset Based on Empty Records

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

Respected Advisor
Posts: 3,156

Re: Split Dataset Based on Empty Records

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

Ask a Question
Discussion stats
  • 6 replies
  • 311 views
  • 0 likes
  • 6 in conversation