Creating a dataset from proc freq output of multiple variables

[HyunJee]

I start out with a dataset containg the following variables

HPV1          HPV2          HPV3          HPV4

1                   2               1                    2

1                    2               1                    2

2                    1               1                   1

2                    1               1                    2

What I want to create is a dataset that looks as follows

HPV          Percent 1          Percent 2          total N

HPV1          50                    50                         4

HPV2          50                     50                         4

HPV3          100                     0                          4

HPV4          25                         75                     4        

I am able to get the percentages I want using proc freq, but I cannot figure out how to create a dataset that contains the data the way I need it organized. I typically output all the individual proc freq for HPV1, HPV2, HPV3 and HPV4 then manually cut and paste and format the data the way I need, but I figured there was probably a way to do this all in SAS. Thank you for any help you can provide.

HyunJee

[Linlin · Accepted solution]

data have;

input hPV1-HPV4;

cards;

1 2 1 2

1 2 1 2

2 1 1 1

2 1 1 2

;

run;

data _null_ ;

   if _n_=1 then set have nobs=nobs;

   call symputx('n',nobs);

   stop;

   run;

%put &n;

%macro test;

%do i=1 %to 4;

proc freq data = have noprint;

tables hpv&i/ out = out;

run;

proc transpose data=out out=out&i (drop=_label_ _name_);

var  percent;

id hpv&i;

run;

data out&i;

  set out&i;

  hpv="hpv&i";

%end;

data want(rename=(_1=percent1 _2=percent2));

  set %do i=1 %to 4;

    out&i %end;;

      if _1=. then _1=0;

      if _2=. then _2=0;

      total=&n;

%mend;

%test

options nocenter;

proc print data=want ;run;

Obs    percent1    percent2    hpv     total

1         50         50       hpv1      4

2         50         50       hpv2      4

3        100          0       hpv3      4

4         25         75       hpv4      4

Linlin

[art297]

Absolutely nothing wrong with learn and using macros but, in this case, I think the problem is solved a lot easier and quicker obtaining and modifying the ods output.  E.g.:

data have;

input hPV1-HPV4;

cards;

1 2 1 2

1 2 1 2

2 1 1 1

2 1 1 2

;

proc freq data=have;

  tables _all_;

  ods output OneWayFreqs=want;

run;

data want (keep=hpv per: tot:);

  set want (rename=(table=hpv percent=_percent));

  by hpv notsorted;

  array percent(2);

  retain percent:;

  variable_name = scan(hpv,-1,' ');

  if first.hpv then do i=1 to 2;

    percent(i)=0;

  end;

  percent(vvalueX(variable_name))=_percent;

  if last.hpv then do;

    Total_N=CumFrequency;

    hpv=substr(hpv,7);

    output;

  end;

run;

[Ksharp]

data have;
input HPV1-HPV4;
cards;
1 2 1 2
1 2 1 2
2 1 1 1
2 1 1 2
;
run;

ods listing close;
ods output OneWayFreqs=temp;
proc freq data=have;
  tables _all_/nocum ;
run;
ods listing;
data x(keep=table value frequency percent);
 set temp;
 table=scan(table,2);
 value=coalesce(of hpv:);
run;
proc transpose data=x out=want1(drop=_name_) prefix=percent;
 by table;
 id value;
 var percent;
run;
proc means data=x noprint nway;
 class table;
 var frequency;
 output out=want2(drop=_:) sum=total;
run;
data want(rename=(table=hpv));
 merge want1 want2;
 by table;
run;

Ksharp