SQL script in SAS

[Smitha9]

Hi,

I have the below SQL script and I want to run in SAS. I have zero knowledge in SQL. Could you help me how to do it in SAS? thanks.

select * from try..a_RAW;

　

select distinct

'11111' as ID

, a.SUBJID

, b.DOB_M || '/' || b.DOB_D || '/' || b.DOB_Y as DOB -- study team need DOB

, a.car as type

, a.g6m as month

, a.g2yr as 24month

, a.g1yr as 12month

, a.g10yr as decade

from try..a_RAW a

join try..FINAL b on b.ID = '11111' and a.SNO = b.SNO

;

　

[PaigeMiller]

What is the exact question here? Is the problem you don't know how to handle DOB? Or something else? Be specific.

[Smitha9]

Hi, I want to copy this sql scirpt and paste it in the SAS and run it. Can I do that? is there any procedure for this?

[PaigeMiller]

So it looks like @PGStats has answered the big question — but are there really 3 variables DOB_Y and DOB_M and DOB_D?

That's a very poor way of representing dates, and depending on whether or not these are character or numeric, you would be better off with something like this (assuming they are numeric)

mdy(dob_m,dob_d,dob_y) as dob format=mmddyys8.

[PGStats]

Correct syntax would look like:

proc sql;
create table want as

select distinct
	'11111' as ID
	, a.SUBJID
	, catx("/", b.DOB_M, b.DOB_D, b.DOB_Y) as DOB  /* study team need DOB */
	, a.car as type
	, a.g6m as month
	, a.g2yr as 24month
	, a.g1yr as 12month
	, a.g10yr as decade
from 
	try.a_RAW as a inner join 
	try.FINAL as b on a.SNO = b.SNO
where b.ID = "11111"
;
quit;

[ballardw]

Do you want to create an output data set or report?

What is the library name that the data set you want to work with resides in? The name of the data set? Are those the variables in your SAS data set?

If you have data in a SAS data set you reference it on the From clauses as libname.datasetname, one dot.

SAS provides a Proc SQL that runs ANSII standard sql statements generally.

So to use that Proc for a simple select to send a report to the SAS results:

Proc sql;
   select *
   from sashelp.class
   ;
quit;

will send the contents of the data set CLASS in the SASHELP library to the results window. You can copy that code an run it as your SAS install should provide that data set.

To make another data set from an existing one you add a "create table" clause

Proc sql;
    create table work.class as
    select *
    from work.class
   ;
quit;

With the above examples you should be able to run that script against SAS datasets with the proper data set names and variables to get output by modifying your code and running Proc SQL as shown. Note you use QUIT; to end the procedure, not Run.

[Tom]

To run SQL code in SAS you need to use PROC SQL.

You need to make sure your SQL is compatible with SAS.  Every implementation of SQL has it own distinct features and quirks.

The main issue with your posted code is the strange text at the end of one of the lines.  Perhaps the SQL implementation that was used to run that code in the past allowed some type of "end of line" commenting and those double dashes are the trigger for that?  I do not like end of line comments in general, you should place the comments before the code they are describing if you want to make it easier for humans to read the code.

Another issue is why would you run code like that without directing the output to a dataset.  If you want to print the data, like that first query, then just use PROC PRINT and skip SQL completely.

Also to reference a dataset from a library you use only one period between the libref and the member name.

Also if you have month day and year why not just make an actual DATE variable?

proc sql;
create table copy_of_raw as
  select * from try.a_RAW
;

create table raw_with_final as
select distinct
  '11111' as ID
, a.SUBJID
/* study team need DOB */
, mdy( b.DOB_M , b.DOB_D , b.DOB_Y) as DOB format=date9.
, a.car as type
, a.g6m as month
, a.g2yr as 24month
, a.g1yr as 12month
, a.g10yr as decade
from try.a_RAW a
join try.FINAL b
  on b.ID = '11111'
 and a.SNO = b.SNO
;
quit;