topic Re: Proc means to roll up data or datastep?? in SAS Programming

Proc means to roll up data or datastep??

Q1983 — Wed, 30 Oct 2019 20:17:40 GMT

data test2;

set steps;

by ln_no;

if ls_step_cd = '543' then _543=ls_actual_compl_dt ;

if ls_step_cd = '843' then _843=ls_actual_compl_dt;

if ls_step_cd = 'K04' then _K04=ls_actual_compl_dt;

if ln_no = '111111111';

if ls_actual_compl_dt ne .;

drop ls_step_cd ls_actual_compl_dt REC_CHNG_CD;

format _543 _843 _K04 mmddyy10.;

run;

Produces this output

LN_NO

_543

_843

_K04

111111111

09/26/2017

111111111

03/30/2018

111111111

09/18/2018

111111111

09/21/2018

111111111

03/25/2019

111111111

10/03/2017

111111111

04/17/2018

111111111

09/26/2018

111111111

03/27/2019

111111111

10/03/2017

111111111

04/17/2018

111111111

09/26/2018

111111111

03/27/2019

111111111

10/08/2019

Desired Output

LN_NO

_543

_843

_K04

111111111

09/26/2017

10/03/2017

111111111

03/30/2018

04/17/2018

111111111

09/18/2018

09/26/2018

111111111

09/21/2018

03/27/2019

111111111

03/25/2019

10/08/2019

Essentially I want to rollup the first instance of each on the same line horizontally and subsequent rows almost like a waterfall display

Is there a way to accomplish this?

Re: Proc means to roll up data or datastep??

Jagadishkatam — Wed, 30 Oct 2019 20:29:07 GMT

Please try the below untested code

data test2;
set steps;
by ln_no;
if ln_no = '111111111';
if ls_actual_compl_dt ne .;
drop ls_step_cd ls_actual_compl_dt REC_CHNG_CD; 
format _543 _843 _K04 mmddyy10.;
run;

proc sort data=test2;
by ln_no;
run;

proc transpose data=test2 out=want prefix=_;
by ln_no;
id ls_step_cd;
var ls_actual_compl_dt;
run;

Re: Proc means to roll up data or datastep??

Reeza — Wed, 30 Oct 2019 20:31:11 GMT

Show what the input data looks like?
Do you want a report or table?
If report, use TABULATE or REPORT

If table, PROC MEANS + TRANSPOSE most likely.

Re: Proc means to roll up data or datastep??

Reeza — Wed, 30 Oct 2019 20:41:20 GMT

proc report data=steps;
columns ln_no ls_step_cd rec_chng_cd;

define ln_no / display;
define ls_step_cd / ACROSS;
define rec_chng_cd / analysis format=date9.;

run;

Not sure if this will work, but it may. Otherwise, if you're not actually summarizing a PROC TRANSPOSE is all you need.

Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/

https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/

Re: Proc means to roll up data or datastep??

Astounding — Wed, 30 Oct 2019 21:44:00 GMT

This ought to work, but it's untested:

data want;
merge have (where=(ln_no='111111111'  and ls_actual_compl_dt ne . and ls_step_cd='543')
            rename=(ls_step_cd=_543) )
      have (where=(ln_no='111111111'  and ls_actual_compl_dt ne . and ls_step_cd='843')
            rename=(ls_step_cd=_843) )
      have (where=(ln_no='111111111'  and ls_actual_compl_dt ne . and ls_step_cd='K04')
            rename=(ls_step_cd=_K04) );
run;

Re: Proc means to roll up data or datastep??

hashman — Wed, 30 Oct 2019 22:21:44 GMT

@Q1983:

One way it can be approached is this - assuming that the input file is sorted by the line number and code:

data have ;                                                                                                                             
  input ln_no cd :$3. dt :mmddyy10. ;                                                                                                   
  format dt yymmdd10. ;                                                                                                                 
  cards ;                                                                                                                               
1  543   09/26/2017                                                                                                                     
1  543   03/30/2018                                                                                                                     
1  543   09/18/2018                                                                                                                     
1  543   09/21/2018                                                                                                                     
1  543   03/25/2019                                                                                                                     
1  843   10/03/2017                                                                                                                     
1  843   04/17/2018                                                                                                                     
1  843   09/26/2018                                                                                                                     
1  843   03/27/2019                                                                                                                     
1  K04   10/03/2017                                                                                                                     
1  K04   04/17/2018                                                                                                                     
1  K04   09/26/2018                                                                                                                     
1  K04   03/27/2019                                                                                                                     
1  K04   10/08/2019                                                                                                                     
2  543   03/30/2018                                                                                                                     
2  543   09/18/2018                                                                                                                     
2  543   09/21/2018                                                                                                                     
2  543   03/25/2019                                                                                                                     
2  843   10/03/2017                                                                                                                     
2  843   09/26/2018                                                                                                                     
2  843   03/27/2019                                                                                                                     
2  K04   10/03/2017                                                                                                                     
2  K04   03/27/2019                                                                                                                     
2  K04   10/08/2019                                                                                                                     
;                                                                                                                                       
run ;                                                                                                                                   
                                                                                                                                        
data v / view = v ;                                                                                                                     
  do q = 1 by 1 until (last.cd) ;                                                                                                       
    set have ;                                                                                                                          
    by ln_no cd ;                                                                                                                       
    output ;                                                                                                                            
  end ;                                                                                                                                 
run ;                                                                                                                                   
                                                                                                                                        
data _null_ ;                                                                                                                           
  if 0 then set have ;                                                                                                                  
  dcl hash h (dataset:"have") ;                                                                                                         
  h.definekey ("cd") ;                                                                                                                  
  h.definedone () ;                                                                                                                     
  dcl hiter i ("h") ;                                                                                                                   
  call execute ("data want(drop=q); merge") ;                                                                                           
  do while (i.next() = 0) ;                                                                                                             
    call execute (cats ("v (where=(cd='", cd, "') rename=dt=_", cd, ")")) ;                                                             
  end ;                                                                                                                                 
  call execute ("; by ln_no q; run;") ;                                                                                                 
  stop ;                                                                                                                                
run ;

The sequence variable q created in the view V takes care of merging the subfiles in such a way that the missing date values in the output appear where they are needed.

Kind regards

Paul D.

Re: Proc means to roll up data or datastep??

ballardw — Wed, 30 Oct 2019 22:28:05 GMT

I would suggest showing an actual example of your STEPS data set. You might be overcomplicating things.

Especially if you need to do this for multiple values of LN_NO.

Re: Proc means to roll up data or datastep??

Q1983 — Sat, 02 Nov 2019 21:55:44 GMT

Thanks, your solution appear to work. As a followup. I took your code and present a modified version here

options missing= .;

data have;

input ln

setup_dt :mmddyy10.

step :$3.

step_dt :mmddyy10.

rem_dt :mmddyy10.;

format setup_dt step_dt rem_dt mmddyy10.;

cards;

1 08/15/2018 550 10/11/2018 12/05/2018

1 08/15/2018 552 09/12/2019 11/05/2019

1 06/11/2019 550 06/11/2019 08/02/2019

;run;

proc sort data= have;by ln step;run;

data v / view = v ;

do q = 1 by 1 until (last.step) ;

set have ;

by ln step ;

output ;

end ;

run ;

data _null_ ;

if 0 then set have ;

dcl hash h (dataset:"have") ;

h.definekey ("step") ;

h.definedone () ;

dcl hiter i ("h") ;

call execute ("data hav2(drop=q); merge") ;

do while (i.next() = 0) ;

call execute (cats ("v (where=(step='", step, "') rename=step_dt=_", step, ")")) ;

end ;

call execute ("; by ln q; run;") ;

stop ;

run ;

Output here

ln	setup_dt	step	_550	_552	rem_dt
1	08/15/2018	552	10/11/2018	09/12/2019	11/05/2019
1	06/11/2019	550	06/11/2019		08/02/2019

Is there a way to loop horizontally and show the month between setup_dt and rem_dt For example show Aug2018,Sep2018,Oct2018,until Nov2019 for row one. The dates can be shown as a text field, then place a 1 if the condition is true. Something like this

ln	setup_dt	step	_550	_552	rem_dt	Aug-18	30-Sep	18-Oct	loop until Nov 2019
1	08/15/2018	552	10/11/2018	09/12/2019	11/05/2019	1	1	1
1	06/11/2019	550	06/11/2019		08/02/2019	1	1	1	loop until 8/2019