topic Re: sas data step in SAS Programming

sas data step

mrinmoy — Thu, 15 Apr 2021 15:06:28 GMT

Hi Everyone I need a help to solve the below problem, Please help me on this ASAP.
 
I have data like below
 
data ab;
input id$ category$ visit mmddyyyy10. ;
cards;
100A xxx 2/1/2020
100A aa 3/1/2020
300C bb 6/2/2020
100A xxx 7/1/2020
200D ccc 9/3/2020
200D ccc 10/3/2020
400E ccc 10/2/2020
100A ddd 11/1/2020
;
run;
Below are the sample of required output.
id           xxx            aa            bb            ccc                  ddd
100A 2/1/2020 3/1/2020                                            11/1/2020
200D                                                    9/3/2020
300C                                   6/2/2020
400E                                                     10/2/2020
 
 
###My desired out put is a dataset###
Below is the query which is giving me required out put but i need help to make it in data step
select distinct * from (
SELECT
OPPORTUNITYID,
UPPER(STAGENAME) as STAGENAME,
CAST(CREATEDDATE as DATE) as CREATEDDATE
FROM tablea
WHERE UPPER(STAGENAME) in ('PURSUE','PITCH','LOW','MED','HIGH','CLOSED','LOST')
AND STAGENAME ne ''
AND t_date =(select max(t_date) from tablea)
) a
pivot (MIN(CREATEDDATE) for STAGENAME in (
'PURSUE' as PURSUE_DT,
'PITCH' as PITCH_DT,
'LOW' as EL_DT,
'MED' as EM_DT,
'HIGH' as EH_DT,
'CLOSED' as CLOSED_DT,
'LOST' as LST_DT)
) as Stages
 
 

Re: sas data step

andreas_lds — Thu, 15 Apr 2021 05:15:52 GMT

If you want something "asap" you should, at least, provide data in usable form.

Is the order of the new variables important?

Why do you need all those new variables at all? Or: having category and date in the output is confusing, especially for id = "100A" printing the second obs seems useless, because all information is already in the first obs.

Re: sas data step

mrinmoy — Thu, 15 Apr 2021 08:17:11 GMT

Second obs 100A is required because it's category is different and I need minimum date..new variable order could be in any order...

Re: sas data step

Kurt_Bremser — Thu, 15 Apr 2021 09:45:33 GMT

Please supply your data in a data step with datalines, so we can be sure about variable attributes and content. At the moment, it is not clear if your dates are actually stored as SAS dates, and what the order is (DMY vs. MDY).

Re: sas data step

mrinmoy — Thu, 15 Apr 2021 13:52:19 GMT

data ab;
input id$ category$ visit mmddyyyy10. ;
cards;
100A xxx 2/1/2020
100A aa 3/1/2020
300C bb 6/2/2020
100A xxx 7/1/2020
200D ccc 9/3/2020
200D ccc 10/3/2020
400E ccc 10/2/2020
100A ddd 11/1/2020
;
run;

Below are the sample of required output.

id xxx aa bb ccc ddd
100A 2/1/2020 3/1/2020 11/1/2020
200D 9/3/2020
300C 6/2/2020
400E 10/2/2020

Below is the query which is giving me required out put but i need help to make it in data step
select distinct * from (
SELECT
OPPORTUNITYID,
UPPER(STAGENAME) as STAGENAME,
CAST(CREATEDDATE as DATE) as CREATEDDATE
FROM tablea
WHERE UPPER(STAGENAME) in ('PURSUE','PITCH','LOW','MED','HIGH','CLOSED','LOST')
AND STAGENAME ne ''
AND t_date =(select max(t_date) from tablea)
) a
pivot (MIN(CREATEDDATE) for STAGENAME in (
'PURSUE' as PURSUE_DT,
'PITCH' as PITCH_DT,
'LOW' as EL_DT,
'MED' as EM_DT,
'HIGH' as EH_DT,
'CLOSED' as CLOSED_DT,
'LOST' as LST_DT)
) as Stages

Re: sas data step

ballardw — Thu, 15 Apr 2021 14:40:21 GMT

Is the desired output a data set or a report for people to read?

If a report then one way is:

proc tabulate data = ab;
   class id category;
   var visit;
   table id='',
         category=''*visit=' '*max=' '*f=mmddyy10.
         / misstext=' '  box=id
   ;
run;

Re: sas data step

mrinmoy — Thu, 15 Apr 2021 15:04:16 GMT

Desired output is a dataset

Re: sas data step

Kurt_Bremser — Thu, 15 Apr 2021 15:21:27 GMT

@mrinmoy wrote:

data ab;
input id$ category$ visit mmddyyyy10. ;
cards;
100A xxx 2/1/2020
100A aa 3/1/2020
300C bb 6/2/2020
100A xxx 7/1/2020
200D ccc 9/3/2020
200D ccc 10/3/2020
400E ccc 10/2/2020
100A ddd 11/1/2020
;

Log from that:

 73         data ab;
 74         input id$ category$ visit mmddyyyy10. ;
                                      ___________
                                      485
 NOTE 485-185: Informat MMDDYYYY was not found or could not be loaded.
 
 75         cards;
 
 NOTE: Invalid data for visit in line 76 10-17.
 REGEL:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                     
 76         100A xxx 2/1/2020

I obviously forgot to add "working".