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_NUM | APPSTATUS_ | APPSTATUSDATE | DECISION |
27516 | 2016 | 20May2016 13:53:24.000 | 4 |
27516 | 2016 | 20May2016 13:55:00.000 | 2 |
27516 | 2016 | 20May2016 13:55:39.000 | 3 |
27516 | 2016 | 20May2016 14:39:27.000 | 1 |
27516 | 2016 | 20May2016 15:55:05.000 | 3 |
27516 | 2016 | 21May2016 7:18:01.000 | 1 |
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.
Yes, the datetime is a SAS format. The output is like what you said. Here is more data from the sample
CLASS_NUM | YEAR | APPSTATUSDATE | DECISION |
1850 | 2016 | 21Apr2016 18:26:26.000 | 2 |
3350 | 2016 | 13Jan2016 13:57:21.000 | 4 |
3350 | 2016 | 13Jan2016 13:58:38.000 | 1 |
3350 | 2016 | 31Mar2016 11:27:06.000 | 3 |
3350 | 2016 | 31Mar2016 13:32:53.000 | 1 |
3350 | 2016 | 31Mar2016 18:34:43.000 | 4 |
12370 | 2016 | 31Mar2016 18:35:10.000 | 3 |
12370 | 2016 | 01Apr2016 13:37:52.000 | 3 |
12370 | 2016 | 01Apr2016 14:07:27.000 | 3 |
12370 | 2016 | 01Apr2016 14:10:55.000 | 1 |
14386 | 2016 | 06Jul2016 16:38:08.000 | 2 |
14386 | 2016 | 08Jul2016 18:41:50.000 | 1 |
14386 | 2016 | 08Jul2016 18:47:51.000 | 5 |
14386 | 2016 | 25Feb2016 16:37:40.000 | 1 |
15853 | 2016 | 22Feb2016 12:38:33.000 | 2 |
15853 | 2016 | 22Feb2016 12:38:48.000 | 5 |
15870 | 2016 | 01Dec2015 15:01:34.000 | 1 |
17907 | 2016 | 11May2016 11:56:13.000 | 1 |
17970 | 2016 | ||
18165 | 2016 | 13Jun2016 17:05:00.000 | 3 |
18165 | 2016 | 14Jun2016 9:36:05.000 | 1 |
18165 | 2016 | 19Jun2016 13:16:38.000 | 5 |
27516 | 2016 | 20Apr2016 13:53:24.000 | 4 |
27516 | 2016 | 20Apr2016 13:55:00.000 | 2 |
27516 | 2016 | 20Apr2016 13:55:39.000 | 3 |
27516 | 2016 | 20Apr2016 14:39:27.000 | 1 |
27516 | 2016 | 20Apr2016 15:55:05.000 | 3 |
27516 | 2016 | 21Apr2016 7:18:01.000 | 1 |
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.