BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HyunJee
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

View solution in original post

3 REPLIES 3
Linlin
Lapis Lazuli | Level 10

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
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1170 views
  • 7 likes
  • 4 in conversation