adding extra row

[sam369]

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

[stat_sas]

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;

[Reeza]

Can you use proc tabulate with a preloadfmt instead?

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

[Cynthia_sas]

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

cynthia

[Ksharp · Accepted solution]

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

[sam369]

Hi Ksharp,

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

Hi All,

Thank you for your valuable input..

Sam

[data_null__]

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;