Questions about proc SQL & proc transpose

[deleted_user]

I am new to SAS, can anyone help me with this data like this?

Table 1
ID DRUG DAY MONTH YEAR
1 A . 3 2005
1 A . 3 2005
2 E . . .
3 B 10 4 2006
4 C 5 . .
4 D 6 12 2005

I have two questions;
#1 Actually, it is already sas dataset, I wanted to use the following SAS code to combine day month and year together, but I failed. '.' stands for missing values.
data work.a; set work.b;
IF DAY NE '.' and MONTH NE'.' and YEAR NE '.'
then date =mdy(DATA,MONTH,YEAR); else date='.';
run;
And I thought probably I needed SQL first, but I did not know how to use it?


#2. How to use transpose to make the data like this:

Table 2
ID DRUG DATE
1 A 12345
2 B 12345
3 B 1.
4 D 12345
5 C 12345

I used the following SAS code for Drug in table 1
proc transpose data=work.a out=work.c
prefix=drug;
id ID;
run;

Then there was an error:
Error: The drug value "A" occurs twice

I am very appreciated for your help.

[sbb]

Check the syntax of your SAS code using the MDY function - the variables are not correct for each argument. Also, the DATE variable is going to be a SAS numeric variable, with it representing a DATE, so you need not refer to the variable DATE with quotes surrounding the "missing value" condition.

Read up on using the ID statement -- just as the diagnostic message states, you have an ID statement code yet you have two observations with the same value for variable DRUG. Maybe you also want DRUG in the ID statement? Maybe not.


Scott Barry
SBBWorks, Inc.