HELP

[Soha]

Suppose my dataset has variables v1 ad v2 along with other variables. The variable v1 contains date values. Suppose the record number 1 has a value of date1 and record number two also has a value of date1 and the values for the variable v2 are different for these two records. Now, I want the dataset such that there is just one record with variable v1 having the value date1 and two other variables v2 and v3. Variable v2 must contain the same value as it had for record 1. Variable v3 must have the value which variable v2 had in record 2. Can you please help me with a code for this?
Example: This is how my dataset is at present.
v1 v2
6/2/2005 Tornado
6/2/2005 Hail



I want my dataset to be changed in following way:

v1 v2 v3
6/2/2005 Tornado Hail


Above is the example of the dataset. The datset can contain same date with multiple storm conditions and not just two stotm conditions.
Can you please help me how to code this?

[Cynthia_sas]

Hi:
If you had this data:
[pre]
v1 v2
6/2/2005 Tornado
6/2/2005 Hail
6/3/2005 Rain
6/3/2005 Cloudy
6/4/2005 Cloudy
6/5/2005 Hail
6/5/2005 Tornado
6/5/2005 Rain
[/pre]

In a SAS dataset entitled WORK.WEATHER, then you could quite simply use the transpose procedure to change the structure of the data.
[pre]

proc transpose data=work.weather out=work.w_trans;
by v1;
var v2;
run;

options nocenter;
proc print data=work.w_trans;
title 'transposed data';
format v1 mmddyy10.;
run;

[/pre]

And the results from the transpose look like this:
[pre]
transposed data

Obs v1 _NAME_ COL1 COL2 COL3

1 06/02/2005 v2 Tornado Hail
2 06/03/2005 v2 Rain Cloudy
3 06/04/2005 v2 Cloudy
4 06/05/2005 v2 Hail Tornado Rain

[/pre]

Note that PROC TRANSPOSE, by default, calls the transposed variables "COL" and numbers the variables. If you really need to have them named differently, there are ways to rename variables or just relabel the variables to your needs. These methods are part of the fundamental concepts of how SAS stores data and you can find that information in the documentation on PROC DATASETS and/or the RENAME= dataset option. Also note that in my example data, the V1 variable is a numeric variable that represents a SAS date, so a format must be applied to show that number in its date form in the PROC PRINT step. If your V1 variable is a character string, then you would not need a Format statement.

You said that your data had "other variables" -- whether this is the right solution for you depends on what the "other" variables are and whether you need them to be part of the transposed data, too. It may be that PROC TRANSPOSE is not the right procedure for your data and you may need to look to a DATA step program to transpose the data. For more help with this task, your best bet might be to read the documentation or contact Tech Support, especially, if you are not familiar with SAS basic concepts.

cynthia

[deleted_user]

a little bit long, but worth for big data set and different weather conditions.


data yours;
input ddd mmddyy10. weather $20.;
datalines;
06/02/2005 Tornado
06/02/2005 Hail
06/03/2005 Rain
06/03/2005 Cloudy
06/04/2005 Cloudy
06/05/2005 Hail
06/05/2005 Tornado
06/05/2005 Rain
run;

proc sort data=yours; by ddd; run;
proc freq data=yours; table weather/out=out; run;

data _null_;
length weathers $100;
set out end=eof;
retain weathers ' ' cnt 0;
weathers = catx(' ', weathers, weather);
cnt + 1;
if eof then do;
call symputx('weathers', weathers);
call symputx('nWeather', cnt);
end;
run;
%put &weathers &nWeather; * check macro variables;

data new;
set yours;
array vv[&nWeather] &weathers;
by ddd;
retain &weathers;
if first.ddd then do;
do i=1 to &nWeather;
vv = 0;
end;
end;
do i=1 to &nWeather;
if weather = scan("&&weathers", i) then leave;
end;
if i <= &nWeather then vv = 1;
if last.ddd then output;
drop i weather;
format ddd mmddyy10.;
run;