Help using Base SAS procedures

How do I split observations by date?

Reply
Occasional Contributor
Posts: 12

How do I split observations by date?

Hello,

 

I'm very new to SAS 9.4 and just started learning to code.

 

I would like to separate the observations in my data by date (specifically year). My 'date_of_service' variable is in the format of mm/dd/yy but it is entered as just numbers, not as a date format.

 

I tried using the code below to separate 2010 entry from 2011 entry but SAS doesn't recognize the dates.

 

data pei2010 pei2011;
set mylib.peinodup;
if date > '31Dec2010'd then output pei2011;
if date <= '31Dec2010'd then output pei2010;
run;

 

How do I change the format for my 'date_of_service' entries so that it can be recognized as date? When I go to column attributes, it seems like it is in character format.

 

Thanks in advance and please message me if I'm missing something critical.

 

Super Contributor
Posts: 490

Re: How do I split observations by date?


jwill731 wrote:

Hello,

 

I'm very new to SAS 9.4 and just started learning to code.

 

I would like to separate the observations in my data by date (specifically year). My 'date_of_service' variable is in the format of mm/dd/yy but it is entered as just numbers, not as a date format.

 

 

 


If it is as you said then it is recognized as Date by sas.

 

Could you run :

 

proc contents data=mylib.peinodup;
run;

And post the output table "Alphabetic List of Variables and Attributes" values.

 

 

Your code should work fine also if your data is fine. But you should consider that it does not only separate 2010 entry from 2011. But separate (2010 and all before) from (2011 and all after).

Occasional Contributor
Posts: 12

Re: How do I split observations by date?

Posted in reply to mohamed_zaki

Here is the table. It is in the character format and with regards to separating everything before 2010 and after 2011 it is okay. The data only contains 2010 and 2011

Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 1 13 2 5 8 9 14 4 10 12 3 6 7 11
AChar1$1.$1.A
Approved_amtNum8COMMA15.2 Approved_amt
BChar1$1.$1.B
DOBChar7$7.$7.DOB
Date_of_ServiceChar10$10.$10.Date_of_Service
Facility_type_idChar5$5.$5.Facility_type_id
Fee_CodeNum8BEST. Fee_Code
GenderNum8BEST. Gender
ICD_9Char5$5.$5.ICD_9
Paid_amtNum8COMMA15.2 Paid_amt
Patient_IDNum8BEST. Patient_ID
Postal_CodeChar3$3.$3.Postal_Code
RegionChar10$10.$10.Region
SpecialtyNum8BEST. 

Specialty

 

Super Contributor
Posts: 490

Re: How do I split observations by date?

[ Edited ]
data pei2010 pei2011;
set have;
wantvar=input(strip(Date_of_Service),mmddyy8.);
if wantvar > '31Dec2010'd then output pei2011;
if wantvar <= '31Dec2010'd then output pei2010;
run;

This should work. The rest i think you know what you are doing.

Note: mmddyy8. or mmddyy10.  , depend on you date if the year is in two digit or four digit.

Occasional Contributor
Posts: 12

Re: How do I split observations by date?

Posted in reply to mohamed_zaki

Okay thanks a lot! I will try this out

Valued Guide
Posts: 2,177

Re: How do I split observations by date?

The data can be separated without INPUT() conversion.
For example
If scan( date_of_service, -1 , '/' ) = '2010' then output pei2010 ; else output pei2011 ;
However having the value stored in standard SAS date form will support downstream reporting far better than that string. Unfortunately your source system might choose string storage because some values might not convert well ....
dates before 1582 (a very old church)
or containing strings without year (elderly might remember only when the anniversary falls unsure of that year of birth)
or just uncleansed data
Super User
Posts: 11,343

Re: How do I split observations by date?

[ Edited ]

One very powerful feature of SAS that you may not be aware of is BY group processing. If you are separating the data and intend to do the same steps to each set then the separation may not be needed. Add a YEAR variable to the data and then most procedures allow use of BY Year; to create the output for each level of the Year variable.

 

And for many actions if the date is a SAS date value (as the WANTVAR create by @mohamed_zaki) you can use a format to create groups 'on the fly' for processing. A brief example:

 

data example;
   set have;
   wantvar=input(strip(Date_of_Service),mmddyy8.);
run;

proc means data= example max min std sum;
   class wantvar;
   format wantvar year4.;
   var Paid_amt;
run;

creates a summary for each year in the data.

A change in format such as to YYMM or YYMON would create a monthy summary with year information, or YYQ to get get calendar quarters.

Ask a Question
Discussion stats
  • 6 replies
  • 401 views
  • 0 likes
  • 4 in conversation