Change data set structure from "FROM_date" "Till_date" information to "Date" information

[Ronein]

Hello

I have a summary table that contain for each interval of dates the group.(It is an external data set that I receive).

I need to create from this summary table another summary table that contain information for each date what is the group.

What is the best way to do it please?

[ErikLund_Jensen · Accepted solution]

Hi @Ronein 

You can make use of the output statement. SAS outputs the current value of variables whenever an output statement is executed, so you can place the output statement in a loop over the interval and get an output observation for each iteration.

And please provide working code to create test input. Creating test data was much more work than answering your question,. Note also that it is a bad idea to use reserved words as variable names. The loop control information "i = from to to" doesn't look good, even if it works.

data have;
	from = '15oct2019'd; to = '19oct2019'd; group = 'a'; output;
	from = '20oct2019'd; to = '15nov2019'd; group = 'b'; output;
	from = '16oct2019'd; to = '05dec2019'd; group = 'c'; output;
	from = '06dec2019'd; to = '31dec2019'd; group = 'd'; output;
run;

data want (drop = from to); set have;
	format date date9.;
	do date = from to to;
		output;
	end;
run;

[Ronein]

Thank you so much!

[Ronein]

I didn't run the code because I don't have SAS at home.

I just want to ask please If in your output there is also field "group"??

[ErikLund_Jensen]

Hi @Ronein 

The variable group is included. I made a copy of the code with a lot of comments to explain what is is giong on, so you can see why.

* DATA declares an output data set and specify variables to DROP from output;
DATA want (DROP = from to); 

	* SET declares input data set; 
	* SET also implies an automatic loop over the input data set, so the full program 
	*     is executed once for each observation in input;
	* SET brings all variables from the current input observation into the program,
	*     this includes the variable "group";
	SET have;

	* FORMAT is there only to display the output date in readable form;
	FORMAT date date9.;

	* DO starts a loop that runs for each observation. The variable "date" is incremented by 1
        from the value of "from" until it adds up to the value of "to";
	DO date = from to to;

		* OUTPUT writes all variables in the program to the output data set
			(excluding "from" and "to" named in the DROP option to DATA);
        * The result is that an output observation is written for each "date" 
			between "from" and "to" and repeated for all input observations;
		* The output includes the variable "date", because it it is declared in the DO statement, 
	          the variable "group", because it is read into the program in the SET statement;
		OUTPUT;

	* END ends the code to execute repeatedly in the DO loop for each input observation;
	end; 

* RUN ends the DATA step and thus the code to execute once for each input observation;
RUN;

[Ronein]

Thank you so so much