topic Re: coding help in SAS Programming

coding help

luvscandy27 — Sun, 12 Jan 2020 00:38:53 GMT

Hello everyone!. I'm new to posting and I am not a SAS expert so please bear with me.

I have a code which I run monthly for a 13 month time period (i.e. if i run the code in October it will cover OCT 2018 to OCT 2019. The goal of the code is to determine how many birds were registered within a year of the enter date and how many birds were registered within the grace period which is a year plus 90 days of the enter date. After that is determined I calculate a compliant
percentage by adding the total birds registered within the year plus total birds registered within the grace period and divide by the total population for that month. The population totals come from a proc freq which is made in an earlier step which is not mention below.The mon_yr variable is also made in a previous step which is not listed below.

The output is created using proc report by simply displaying each variable. Any suggestions would be greatly appreciated.

Beginning Data
ENTERDATE BIRDTYPES MON_YEAR ID REGDATE poptotal
7/31/2019 BLUEBIRD JUL_2019 100 11/2/2019 2
7/31/2019 BLUEBIRD JUL_2019 200 12/8/2018 2
8/31/2019 ROBIN AUG_2019 300 12/19/2017 2
8/31/2019 ROBIN AUG_2019 400 9/19/2019 2
9/31/2018 SPARROW SEP_2018 500 6/8/2019 2
9/31/2018 SPARROW SEP_2018 600 4/13/2019 2

SAS Code

/*in order to be considered compliant the birds would need to be registered within 365 plus 90 days of the file date*/

/*in order to be considered compliant the birds would need to be registered within 365 plus 
90 days of the enter date*/

/*calculate relevant start and end dates*/
data test1;
	set test0;

	startdate = (intnx ('month', enterdate, -12, 'e'));
	endate = (intnx ('month' , enterdate, -0, 'e' ));
	extentiondate = (intnx ('month' , enterdate, -15, 'e' ));
	
format startdate enddate extentiondate mmddyy10.;
	
run;

/*check dates*/
proc freq data = test1; 
	table birdtypes * startdate* enddate* extentiondate / list missing;
run; 

/*flag which time frame the birds registration is in */
data test2;
	set test1;

	if startdate le regdate le enddate then withinyr = 1;
		else if extentiondate le regdate le endate then withingrace = 1;
			else outsidetimeframe = 1;
run;

/*calculate percentages*/
proc sql;
	create table test3 as
	select distinct birdtypes, mon_yr, sum(withinyr = 1) as reg_yr,
	sum(withingrace = 1) as regrace, sum(outsidetimeframe = 1) as noncompliant, 
	calculated reg_yr + regrace = compliancetotal, 
	calculated compliancetotal/poptotal = percent_compliant
from test2
group by birdtypes, mon_yr;
quit;

Re: coding help

Reeza — Sun, 12 Jan 2020 00:51:10 GMT

What exactly is your question here? is there some part that's not working? Are you looking to make it more efficient? Does your code not currently answer your question? Do you just want to make sure it's correct?

Re: coding help

luvscandy27 — Sun, 12 Jan 2020 01:17:05 GMT

The code works, I would like to make it more efficient.

#- Please type your reply above this line. No attachments. -##

Re: coding help

Reeza — Sun, 12 Jan 2020 01:33:05 GMT

Questions then:

The goal of the code is to determine how many birds were registered within a year of the enter date and how many birds were registered within the grace period which is a year plus 90 days of the enter date. After that is determined I calculate a compliant

regDate = Registration Date

EnterDate = Date entered in system

Your rule is one year - is that 365 days or a year? In a leap year, such as this, they may not be the same thing. Years and months are not standard measures so 365 + 90 days is not the same as one year plus 3 months so it's very important that these terms are correct, especially if this is used in any actual business processes where someone will call you on it.

Why are you aligning your dates to the end of the month? I don't see any logic in your question that would require that, so not sure why you're doing that in the INTNX(). This could result in the wrong categorization, ie January 1 2019 is more than a year ago, but if you align both dates to the end of the month they become January 31 and it seems like it's within the period.

I do think if you create your dummy variables, withinyr, withingrace, as 0/1 it may be easier to report. If you have binary variables and take the mean of them you get the percentage of success without needing any additional calculations so it could make your process easier.

Can you show what you would expect, EXACTLY, as output given what you've shown as input data?

You may need to expand your input data to more accurately reflect your actual data in this step.

Here are instructions on how to provide sample data as a data step:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: coding help

Astounding — Sun, 12 Jan 2020 03:29:23 GMT

If your real intent is to make the code more efficient, note that you can combine two steps into one. You don't need to create TEST1 and then TEST2. You could instead create your date variables and your flags in a single step:

data test1;
	set test0;

	startdate = (intnx ('month', enterdate, -12, 'e'));
	endate = (intnx ('month' , enterdate, -0, 'e' ));
	extentiondate = (intnx ('month' , enterdate, -15, 'e' ));
	
format startdate enddate extentiondate mmddyy10.;

	if startdate le regdate le enddate then withinyr = 1;
		else if extentiondate le regdate le endate then withingrace = 1;
			else outsidetimeframe = 1;
run;

You can still run the PROC FREQ on the results of this step. This will cut out nearly half the time it takes to run two DATA steps.

Re: coding help

luvscandy27 — Sun, 12 Jan 2020 05:46:11 GMT

It is 365 days plus 90 days.

The enter date is based on the time the data is complied. The data is compiled on the last day of every month. The regdate should not be aligned to the end of the month.

I would like to use binary variables and use your suggestion of "taking the mean of them you get the
percentage of success without needing any additional calculations so it could make your process easier. But maybe do this process
using the month variables.

I provided a sample below of what I think the output should look like M1-M13 represent months 1-13. I
am open to suggestions on how the output should look because I'm really would like to simplify my process and make my code more efficient.

BIRDTYPES ID M1 M2 M3 M4 M5 M6 M7 M8 M9 M10 M11 M12 M13
ROBIN 10 1 0 0 0 0 0 0 0 0 0 0 0 0 0
ROBIN 20 0 1 0 0 0 0 0 0 0 0 0 0 0 0
ROBIN 50 0 0 0 1 0 0 0 0 0 0 0 0 0 0
BLUEBIRD60 0 0 0 0 0 1 0 0 0 0 0 0 0 0
BLUEBIRD70 0 0 0 0 1 0 0 0 0 0 0 0 0 0
BLUEBIRD100 0 0 0 0 0 0 0 1 0 1 0 0 0 0
SPARROW 110 0 0 0 0 0 0 0 0 0 0 1 0 0 0
SPARROW 120 0 0 0 0 0 0 0 0 0 0 0 1 0 0