Help using Base SAS procedures

adding extra row

Accepted Solution Solved
Reply
Regular Contributor
Posts: 168
Accepted Solution

adding extra row

Hi All,

i have a data with level1,level2 level3,level4 categories in a variable. the data is keep on changing. i want to add missing whether the data as missing or not, but my final result reflect missing vategory.

I am using proc format, it is showing when data is missing, if data is no missing final out is not havinf missing category in my final dataset.

And i am ending with creating a dummy dataset to concatenate the with main data.

I am looking for alternative ways to create. Any thoughts will be helpful

example

data have;

input grp subgrp categ cnt;

cards;

3 1 level1 30

3 2 level2 50

3 3 level3 150

3 4 level4 56

3 5 level5 16

;

run;

want:

3 1 level1 30

3 2 level2 50

3 3 level3 150

3 4 level4 56

3 5 level5 16

3 6 missing 0(if data not having missing i need to show 0 or else i need to show how many are missing)

to get the category count i am using freq

proc freq data=rawdata;

  table stklevl/norow nocol missing out=have(rename=(stklevl=categ count=cnt));

format stklevl grpfmt.;

Thanks

Sam


Accepted Solutions
Solution
‎05-21-2014 08:59 AM
Super User
Posts: 9,682

Re: adding extra row

data have;
input grp subgrp categ $ cnt;
cards;
3 1 level1 30
3 2 level2 50
3 3 level3 150
3 4 level4 56
3 5 level5 16
;
run;
data want(drop=n);
 set have end=last;
 output;
 if missing(cnt) then n+1;
 if last then do;
  subgrp=_n_+1;
  categ='missing';
  cnt=n;
  output;
 end;
run;

Xia Keshan

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

Re: adding extra row

Hi,

This is one way to add extra row

data have;
input grp subgrp categ $ cnt;
cards;
3 1 level1 30
3 2 level2 50
3 3 level3 150
3 4 level4 56
3 5 level5 16
;
run;

proc sql;
   insert into have
      set grp=3,
           subgrp=6,
          categ='missing',
          cnt=0;

quit;

proc print data=have;
run;

Super User
Posts: 17,840

Re: adding extra row

Can you use proc tabulate with a preloadfmt instead?

http://support.sas.com/resources/papers/proceedings11/239-2011.pdf

SAS Super FREQ
Posts: 8,743

Re: adding extra row

Preloadfmt works with PROC TABULATE, PROC REPORT and PROC MEANS.

cynthia

Solution
‎05-21-2014 08:59 AM
Super User
Posts: 9,682

Re: adding extra row

data have;
input grp subgrp categ $ cnt;
cards;
3 1 level1 30
3 2 level2 50
3 3 level3 150
3 4 level4 56
3 5 level5 16
;
run;
data want(drop=n);
 set have end=last;
 output;
 if missing(cnt) then n+1;
 if last then do;
  subgrp=_n_+1;
  categ='missing';
  cnt=n;
  output;
 end;
run;

Xia Keshan

Regular Contributor
Posts: 168

Re: adding extra row

Hi Ksharp,

This is the exactly what i am looking for... Thank you !!!

Hi All,

Thank you for your valuable input..

Sam

Respected Advisor
Posts: 3,777

Re: adding extra row

This example show the basics of something from nothing with order.  To include categories that may not exist in the data use PRELOADFMT.   To establish row order and preserve it in PROC FREQ you need ORDER=DATA and MULTILABLE to convert the numeric categorical variable to character.  Otherwise PROC FREQ will move the numeric missing to the first row. There is no actual multi-labeling going on.  This example assumes you want the output from PROC FREQ but you can get this directly from out OUT= with TABULATE.

proc format;
  
value grpfmt(notsorted multilabel)
     
1='level1'
     
2='level2'
     
3='level3'
     
4='level4'
     
5='level5'
     
.='Missing'
      ;
   run;
data have;
   input grp subgrp categ $ cnt;
   cards;
3 1 level1 30
3 2 level2 50
3 3 level3 150
3 4 level4 56
3 5 level5 16
;;;;
run;
proc summary data=have nway missing completetypes;
  
class subgrp / preloadfmt mlf order=data;
   format subgrp grpfmt.;
  
freq cnt;
   output out=summary;
   run;     
proc print;
  
format _all_;
   run;
proc freq data=summary order=data;
   table subgrp  / norow nocol missing out=want noprint;
  
weight _freq_ / zeros;
  
run;
proc print;
  
run;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 291 views
  • 5 likes
  • 6 in conversation