How to combine PROC SQL statement

[learn2learn]

Hi all,

I am very new to SAS . Looking to see the number of patients that have taken a medication only once for between 1-5 days and for what treatment and compare it with the 1995/96 data. The script below is for 2015/16 data.

%_eg_conditional_dropds(WORK."%STR(1516)"n);

PROC SQL;
CREATE TABLE WORK.'1516'n AS
SELECT t1.FYEAR,
t1.DOB,
t1.MEDICATION,
t1.TREATMENT
t1.SEX,
t1.ID,
t1.STARTDATE,
t1.ENDDATE

FROM DRUGDATA.YEAR1516 t1 where MEDICATION=1;
QUIT;

PROC SQL;
create table MED1516
as select t1.DOB,
t1.MEDICATION,
t1.TREATMENT
t1.SEX,
t1.ID,
t1.STARTDATE,
t1.ENDDATE,
ENDDATE-STARTDATE AS amtdaysonmed

FROM DRUGDATA.YEAR1516 t1 where MEDICATION=1;
QUIT;

I have three questions.

1. Is there a way to combine the two steps and still create MED1516?.
2. When I tried to add amtdaysonmed <6 (FROM DRUGDATA.YEAR1516 t1 where MEDICATION=1 and amtdaysonmed <6) it said it couldn't find amtdaysonmed. ERROR: The following columns were not found in the contributing tables: amtdaysonmed. But I can see amtdaysonmed when I checked on output Data.I have tried to do another PROC SQL and add amtdaysonmed but it doesn't work either

3. Is there a way to sort the data by the treatments the drugs are used for?

Thank you for your time

[RW9]

A good start to learning would be to learn the language itself - this is Base SAS.  SQL can be used in a procedure, however it really only has usefulness if you are a) connecting to a database, or b) have some specific join.  

Secondly, starting with macro and macro variables will not teach you anything other than bad coding techniques.

Thirdly, code formatting is far more important than what the code does.  If a user can't read it then its useless.

I would also avoid named literals as much as possible - this being the "..."n.  They are only useful if the database has names which are not SAS compliant, all other times use SAS specific.

Now your code can be written as:

data med1516 (keep=year dob medication treatment sex id startdate enddate amtdaysonmed);
  set drugdata.year1516 in=a 
      drugdata.year1516 in=b
  where medication=1;
  if a then year=.;
  else amtdaysonmed=enddate-startdate;
run;

For point 2, variables are created on the output data, so cannot be used in the current code, unless you use the keyword computed.

For point 3, yes, use proc sort, or if you have to use SQL, order by.

[learn2learn]

Thank you. I am looking at some of the available information online to learn. I am using SAS EG so the output was based on the code suggested from doing it manually. I have some experience working with Stata which seems similar so will try to learn the language instead.

Thanks again for your time