topic Re: Data manipulation - do loops in New SAS User

Data manipulation - do loops

CalebN — Wed, 26 Jun 2019 17:56:19 GMT

Hello,

I'm doing some data manipulation in SAS 9.4 before further analysis. I have several years of temperature data and want to do further calculations if there has been no killing frost in the fall yet. I wrote the following statements to make frost =1 on days where the temperature was less than 2 degrees.

if temp<-2 and day>180 then frost=1;
else frost=0;

Is there a way to make frost =1 for the rest of the days following the first frost in that year, and then reset for the next year?

I was exploring do loops but I'm not sure if that would fit what I'm looking for or not.

Thanks for your help.

Re: Data manipulation - do loops

Reeza — Wed, 26 Jun 2019 17:25:34 GMT

Look at the RETAIN statement.

Re: Data manipulation - do loops

PaigeMiller — Wed, 26 Jun 2019 17:49:59 GMT

Maybe this:

if temp<-2 and day>180 and frost=0 then frost+1;

By the way, it helps if you show a portion of the data. This code fragment and @Reeza 's suggestion work if there is one observation for each day (and so DO loops are not useful here), but it does not work for other layouts of the data (and maybe do loops work there); and we really shouldn't have to guess what your data looks like.

Re: Data manipulation - do loops

Kurt_Bremser — Wed, 26 Jun 2019 17:33:06 GMT

RETAIN and BY group processing. Please supply example data (as advised when you posted).

Re: Data manipulation - do loops

CalebN — Wed, 26 Jun 2019 18:00:20 GMT

Thanks for your responses. I have attached a sample of my data to the original post. I have one temperature observation per day and multiple years.

Re: Data manipulation - do loops

CalebN — Wed, 26 Jun 2019 18:02:02 GMT

I think that code would work for one year however, when the next year starts then frost would not equal zero. How can we make the code "reset" for the next year?

Re: Data manipulation - do loops

Reeza — Wed, 26 Jun 2019 18:03:06 GMT

Depends on the rest of the code. I can't download attachments.

Re: Data manipulation - do loops

Kurt_Bremser — Wed, 26 Jun 2019 18:09:38 GMT

data want;
set have;
by year;
retain frost;
if first.year then frost = 0;
if day > 180 and temp <= 2 then frost = 1;
run;

Re: Data manipulation - do loops

koyelghosh — Wed, 26 Jun 2019 19:29:55 GMT

A little complicated solution (than what has already been suggested) but this is "somewhat" an alternative solution. The data you have shared is for one year only and for first 100 days. So not sure this will work out. Please let me know the outcome. I am curious how was the output of this code.

Thank you.

DATA Temp_Processed;
	SET Temp; /*Change the name for your dataset name*/
	RETAIN Year_Flag 0;
	RETAIN Frost 0;
	IF Year_Flag ^= 0 THEN OUTPUT;
	ELSE IF DAY > 180 and Temp < 2 and Year_Flag=0 THEN DO; Frost = 1; Year_Flag=Year; OUTPUT; END;
	ELSE IF DAY <= 180 THEN DO; Frost = 0; Year_Flag=0; OUTPUT; END;
RUN;

Re: Data manipulation - do loops

CalebN — Wed, 26 Jun 2019 20:02:26 GMT

Thanks for your reply.
Not sure why the file is showing you only 100 days but the .csv I uploaded has a few years with 365 days for each year. When I ran your code, frost did = 1 on the day of the first frost in 1989 but then frost remained 1 for the rest of the days in following years. Not sure why.
Thanks

Re: Data manipulation - do loops

CalebN — Wed, 26 Jun 2019 20:03:03 GMT

This code works for me! Thanks for your help!

Re: Data manipulation - do loops

koyelghosh — Thu, 27 Jun 2019 01:02:44 GMT

I know why because I kept the first condition as not correct. I have changed it. I was tired last night and could not spot it (neither the error nor the fact that the excel file you sent had approximately 1460 rows. SAS Studio just outputs 100 rows at a time, by default. So I am sorry about all that. Here is the code.

DATA Temp_Processed;
	SET Temp;/*Change as per your data name*/
	RETAIN Year_Flag 0;
	RETAIN Frost 0;

	IF Year_Flag ^=0 and DAY > 180 and Temp < 2 THEN
		OUTPUT;
	ELSE IF DAY > 180 and Temp < 2 and Year_Flag=0 THEN
		DO;
			Frost=1;
			Year_Flag=Year;
			OUTPUT;
		END;
	ELSE IF DAY <=180 THEN
		DO;
			Frost=0;
			Year_Flag=0;
			OUTPUT;
		END;
RUN;

You can confirm that for the intended Frost range, it is correctly assigned ... by doing a simple PROC SQL.

PROC SQL;
	SELECT 
		MIN(DAY) AS StartingDate, 
		MAX(DAY) AS EndDate, 
		Year_Flag, 
		Frost 
	FROM 
		Temp_Processed 
	GROUP BY 
	Year_Flag, 
	Frost;
QUIT;

It outputs the intended result.

Sorry again for creating a confusion. Fortunately you got a solution that was working for you and thus your time was not lost by my solution.

Re: Data manipulation - do loops

koyelghosh — Thu, 27 Jun 2019 04:00:58 GMT

Elegant