topic Re: How do I split observations by date? in SAS Procedures

How do I split observations by date?

jwill731 — Tue, 02 Feb 2016 21:58:45 GMT

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.

Re: How do I split observations by date?

mohamed_zaki — Tue, 02 Feb 2016 22:10:10 GMT

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

Re: How do I split observations by date?

jwill731 — Tue, 02 Feb 2016 22:19:04 GMT

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

A	Char	1	$1.	$1.	A
Approved_amt	Num	8	COMMA15.2		Approved_amt
B	Char	1	$1.	$1.	B
DOB	Char	7	$7.	$7.	DOB
Date_of_Service	Char	10	$10.	$10.	Date_of_Service
Facility_type_id	Char	5	$5.	$5.	Facility_type_id
Fee_Code	Num	8	BEST.		Fee_Code
Gender	Num	8	BEST.		Gender
ICD_9	Char	5	$5.	$5.	ICD_9
Paid_amt	Num	8	COMMA15.2		Paid_amt
Patient_ID	Num	8	BEST.		Patient_ID
Postal_Code	Char	3	$3.	$3.	Postal_Code
Region	Char	10	$10.	$10.	Region
Specialty	Num	8	BEST.		Specialty

Re: How do I split observations by date?

mohamed_zaki — Tue, 02 Feb 2016 22:32:11 GMT

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.

Re: How do I split observations by date?

jwill731 — Tue, 02 Feb 2016 23:10:31 GMT

Okay thanks a lot! I will try this out

Re: How do I split observations by date?

Peter_C — Wed, 03 Feb 2016 08:58:06 GMT

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

Re: How do I split observations by date?

ballardw — Wed, 03 Feb 2016 16:50:23 GMT

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.