sas data step

[mrinmoy]

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
 
 

[andreas_lds]

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.

[mrinmoy]

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

[Kurt_Bremser]

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

[mrinmoy]

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

[Kurt_Bremser]

---

@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".

[ballardw]

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;

[mrinmoy]

Desired output is a dataset