topic Re: Getting variable value into summary line using proc report or data step programming in SAS Procedures

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

chanduk — Thu, 23 Mar 2017 08:30:36 GMT

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

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

Kurt_Bremser — Thu, 23 Mar 2017 09:40:13 GMT

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-data-AKA-generate/ta-p/258712

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

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

Ksharp — Thu, 23 Mar 2017 10:12:23 GMT

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;

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

Astounding — Thu, 23 Mar 2017 10:37:45 GMT

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)..

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

chanduk — Thu, 23 Mar 2017 10:53:50 GMT

Thanks for your solution

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

chanduk — Thu, 23 Mar 2017 10:57:02 GMT

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

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

Kurt_Bremser — Thu, 23 Mar 2017 10:59:37 GMT

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