DATA Step, Macro, Functions and more

how to pick specific rows for each application

Reply
Contributor
Posts: 28

how to pick specific rows for each application

Hello,

 

 Here is a small sample of my data with one application. My goal is that for each application, pick the first decision=1 and the lastest decision. Hence for each class_number, it should have two rows. Could anyone help with this? Is there any way to make the vertical row to horizontal? Thank you.

 

CLASS_NUMAPPSTATUS_APPSTATUSDATEDECISION
27516201620May2016 13:53:24.0004
27516201620May2016 13:55:00.0002
27516201620May2016 13:55:39.0003
27516201620May2016 14:39:27.0001
27516201620May2016 15:55:05.0003
27516201621May2016 7:18:01.0001
Super User
Posts: 10,543

Re: how to pick specific rows for each application

What do you want for output if the "first" decision =1 is the "lastest"? Also, is your APPSTATUSDATE variable actually a SAS datetime value or is it character? I ask because the appearance does not match typical SAS datetime format. And by "first" and "lastest" do you mean in relation of the apparent order of APPSTATUSDATE?

So the result for your example data would be:

27516 2016 20May2016 14:39:27.000 1

and

27516 2016 21May2016 7:18:01.000 1

correct?

 

BTW you say "for each application" but there doesn't appear to be any identification for "application".

 

Also it helps to poste data in a format that we can run code against. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

This also will clear up any questions about variable types.

 

Contributor
Posts: 28

Re: how to pick specific rows for each application

Yes, the datetime is a SAS format. The output is like what you said. Here is more data from the sample

 

CLASS_NUMYEARAPPSTATUSDATEDECISION
1850201621Apr2016 18:26:26.0002
3350201613Jan2016 13:57:21.0004
3350201613Jan2016 13:58:38.0001
3350201631Mar2016 11:27:06.0003
3350201631Mar2016 13:32:53.0001
3350201631Mar2016 18:34:43.0004
12370201631Mar2016 18:35:10.0003
12370201601Apr2016 13:37:52.0003
12370201601Apr2016 14:07:27.0003
12370201601Apr2016 14:10:55.0001
14386201606Jul2016 16:38:08.0002
14386201608Jul2016 18:41:50.0001
14386201608Jul2016 18:47:51.0005
14386201625Feb2016 16:37:40.0001
15853201622Feb2016 12:38:33.0002
15853201622Feb2016 12:38:48.0005
15870201601Dec2015 15:01:34.0001
17907201611May2016 11:56:13.0001
179702016  
18165201613Jun2016 17:05:00.0003
18165201614Jun2016 9:36:05.0001
18165201619Jun2016 13:16:38.0005
27516201620Apr2016 13:53:24.0004
27516201620Apr2016 13:55:00.0002
27516201620Apr2016 13:55:39.0003
27516201620Apr2016 14:39:27.0001
27516201620Apr2016 15:55:05.0003
27516201621Apr2016 7:18:01.0001
Super User
Posts: 10,543

Re: how to pick specific rows for each application

Missing values for the datetime are going to make "first" "latest" difficult to define. What should the output be for that classnum=17970.

 

And I don't want to write input statements to read that data. Please look at the link for generating datastep code so we can actually recreate data in the same format that you have.

Ask a Question
Discussion stats
  • 3 replies
  • 108 views
  • 0 likes
  • 2 in conversation