Getting variable value into summary line using proc report or data step programming

[chanduk]

Hi ,

I have a dataset PROV contaiins variables  provider_id ,provider_name,case_id,year .How do I get below desired output using Prov data set.

PROV Data set data:

 provider_id      provider_name     case_id            year

2233              xjlh                        2017-10956      Less than 1 year

2234            xxxx                        2017-10957       Less than 1 year

2235             yyyy                        2017-10958      Less than 1 year

2236             zzzz                        2016-10959      1-2 years old

2236             pppp                        2016-10960     1-2 years old

Desired output :

provider_id  provider_name case_id 

2233                 xjlh              2017-10956

2234                xxxx             2017-10957

2235                yyyy               2017-10958

----------------------------------------------------

Less than 1 year                    3

---------------------------------------------------

2236               zzzz               2016-10959  

2236                pppp             2016-10960  

------------------------------------------------------

1-2 years old                         2

-----------------------------------------------

Regards,

chandu

[Kurt_Bremser · Accepted solution]

First of all, the proper way to present sample data is a data step, as

data prov;
infile cards dlm=',';
input provider_id provider_name $ case_id :$10. year :$20.;
cards;
2233,xjlh,2017-10956,Less than 1 year
2234,xxxx,2017-10957,Less than 1 year
2235,yyyy,2017-10958,Less than 1 year
2236,zzzz,2016-10959,1-2 years old
2236,pppp,2016-10960,1-2 years old
;
run;

This lets everybody recreate your dataset with copy/paste and run, regardless of SAS versions etc.

A macro to help in converting existing datasets to a data step can be found here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

From that, I ran

proc report data=prov;
column provider_id provider_name case_id year provider_id=count;
define year / group order=data noprint;
define provider_id / display;
define provider_name / display;
define case_id / display;
define count / n noprint;
break after year /summarize;
compute after year;
  line year $20. count 5.;
endcomp;
run;

and came pretty close to your intended result (here in listing form):

provider_  provider            
       id  _name     case_id   
     2233  xjlh      2017-10956
     2234  xxxx      2017-10957
     2235  yyyy      2017-10958
   Less than 1 year        3   
     2236  zzzz      2016-10959
     2236  pppp      2016-10960
   1-2 years old           2   

[Ksharp]

data PROV;
input  provider_id     ( provider_name     case_id            year) (& $20.);
cards;
2233              xjlh                        2017-10956      Less than 1 year
2234            xxxx                        2017-10957       Less than 1 year
2235             yyyy                        2017-10958      Less than 1 year
2236             zzzz                        2016-10959      1-2 years old
2236             pppp                        2016-10960     1-2 years old
;
run;

proc report data=prov nowd;
column _all_ n ;
define year/order descending noprint;
define n/noprint;
compute after year;
line @1 year $20. @40  n ;
endcomp;
run;
 

[chanduk]

Thanks for your solution

[Astounding]

To get something easily, you can approximate what you asked for:

proc print data=have n='Duration' label;

by year;

id year;

var provider_id  provider_name case_id;

label year='Duration';

run;

If that's not close enough, it takes a more complex approach (as others have shown)..

[chanduk]

The solution examples which are provided by Ksharp,KurtBremser are working fine

[Kurt_Bremser]

Actually, I like @Ksharp's solution better than mine. He's obviously more versed in proc report.