topic Re: adding extra row in SAS Procedures

adding extra row

sam369 — Tue, 20 May 2014 23:42:04 GMT

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

Re: adding extra row

stat_sas — Wed, 21 May 2014 00:44:25 GMT

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;

Re: adding extra row

Reeza — Wed, 21 May 2014 01:04:35 GMT

Can you use proc tabulate with a preloadfmt instead?

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

Re: adding extra row

Cynthia_sas — Wed, 21 May 2014 02:49:59 GMT

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

cynthia

Re: adding extra row

Ksharp — Wed, 21 May 2014 12:59:54 GMT

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

Re: adding extra row

data_null__ — Wed, 21 May 2014 13:24:03 GMT

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;

Re: adding extra row

sam369 — Wed, 21 May 2014 14:32:39 GMT

Hi Ksharp,

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

Hi All,

Thank you for your valuable input..

Sam