topic Re: Need to keep older quarter data for a site in New SAS User

Need to keep older quarter data for a site

accintron — Wed, 21 Aug 2019 16:11:28 GMT

Hello -- My overall data is from 17 sites. However, as of a Q1_2019 I only have 16 sites. I need the most recent data from all sites for the Total variable. As seen in the resulting dataset below, site 7 is missing because that is the site that ended early (in Q4_2018). I'm trying to bring Site 7 back into my dataset so I can reference its Q4_2018 data. I tried the following code unsuccessfully (I only get all the sites with Q1_2019 data).

data caseld (keep=Site Quarter_Year Total);
set sites;
if (Site=7 and Quarter_Year="Q4_2018") or Quarter_Year="Q1_2019";
run;

I'm wondering if when I create my caseld dataset if there is a way to easily bring Site 7's Q4_2018 data at the same time. I'm doing this so i can run a proc means on the Total variable. Thanks in advance!

This is what my dataset looks like when I run the code above:

Site Total Quarter_Year

1 1 Q1_2019

2 2 Q1_2019

3 3 Q1_2019

4 4 Q1_2019

5 5 Q1_2019

6 6 Q1_2019

8 8 Q1_2019

9 9 Q1_2019

10 10 Q1_2019

......AND SO ON....

Re: Need to keep older quarter data for a site

novinosrin — Wed, 21 Aug 2019 16:03:46 GMT

Hello @accintron Can you please post the data sample in your SITES dataset along with the output(of your code) which you have already posted?

set sites; /*this one*/

Re: Need to keep older quarter data for a site

accintron — Wed, 21 Aug 2019 16:16:45 GMT

Sure, sorry about that. The data is stacked and looks like so:

Site Total Quarter_Year

1 1 Q4_2018
2 2 Q4_2018
3 3 Q4_2018
4 4 Q4_2018
5 5 Q4_2018
6 6 Q4_2018
7 7 Q4_2018
8 8 Q4_2018
9 9 Q4_2018
10 10 Q4_2018
11 11 Q4_2018
12 12 Q4_2018
13 13 Q4_2018
14 14 Q4_2018
15 15 Q4_2018
16 16 Q4_2018
17 17 Q4_2018

1 1 Q1_2019

2 2 Q1_2019

3 3 Q1_2019

4 4 Q1_2019

5 5 Q1_2019

6 6 Q1_2019

8 8 Q1_2019

9 9 Q1_2019

10 10 Q1_2019

11 11 Q1_2019
12 12 Q1_2019
13 13 Q1_2019
14 14 Q1_2019
15 15 Q1_2019
16 16 Q1_2019
17 17 Q1_2019

Re: Need to keep older quarter data for a site

novinosrin — Wed, 21 Aug 2019 16:52:37 GMT

your codes works fine.

here is the test



data sites;
input Site   Total   Quarter_Year $;
cards;
1        1       Q4_2018
2        2       Q4_2018
3        3      Q4_2018
4        4       Q4_2018
5        5       Q4_2018
6        6       Q4_2018
7        7       Q4_2018
8       8       Q4_2018
9       9       Q4_2018
10     10      Q4_2018
11     11       Q4_2018
12     12      Q4_2018
13     13      Q4_2018
14     14      Q4_2018
15     15      Q4_2018
16     16       Q4_2018
17     17       Q4_2018
1        1        Q1_2019
2        2       Q1_2019
3        3       Q1_2019
4        4       Q1_2019
5        5        Q1_2019
6        6        Q1_2019
8        8        Q1_2019
9        9        Q1_2019
10     10       Q1_2019
11     11      Q1_2019
12     12      Q1_2019
13     13      Q1_2019
14     14      Q1_2019
15     15      Q1_2019
16     16      Q1_2019
17     17      Q1_2019
;

data caseld (keep=Site Quarter_Year Total);
set sites;
if (Site=7 and Quarter_Year="Q4_2018") or Quarter_Year="Q1_2019";
run;

RESULT:

7 7 Q4_2018
1 1 Q1_2019
2 2 Q1_2019
3 3 Q1_2019
4 4 Q1_2019
5 5 Q1_2019
6 6 Q1_2019
8 8 Q1_2019
9 9 Q1_2019
10 10 Q1_2019
11 11 Q1_2019
12 12 Q1_2019
13 13 Q1_2019
14 14 Q1_2019
15 15 Q1_2019
16 16 Q1_2019
17 17 Q1_2019

Re: Need to keep older quarter data for a site

accintron — Wed, 21 Aug 2019 17:08:23 GMT

@novinosrinWeird, it doesnt work for me. No errors just I keep returning 16 rows. I tried restarting SAS but it didnt make a difference.

Re: Need to keep older quarter data for a site

novinosrin — Wed, 21 Aug 2019 17:11:55 GMT

Please run a proc contents and check for variable types

Also review a few records to make sure values of the sample posted are exactly the same as your real.

Also maybe it could be a trailing/leading blank problem. So to be on the safe side try to use STRIP function like

if (Site=7 and strip(Quarter_Year)="Q4_2018") or strip(Quarter_Year)="Q1_2019";

Check for case sensitivity as well. Some Q's could be q's

Re: Need to keep older quarter data for a site

accintron — Wed, 21 Aug 2019 17:14:35 GMT

AH nevermind.. I feel so silly... I was referencing the QY value incorrectly for Q4 even though I looked at the data... should have been Q4 2018 not Q4_2018....

Now it does indeed work... Thanks for confirming:)!