Help with PROC SQL

[sms1891]

Hi all,

I am extracting data from a remote server using proc sql, which is kind of new to me. I need help with data extraction step. Any help with the code will be greatly appreciated.

In the coding, I have a where clause, I was wondering how to use multiple variables within the where clause. I have a series of diagnosis code variables, DXPRIME, DXLSF and DXF2 through DXF13 (DXF2 - DXF13). From all these variables, I am trying to extract if they have 157, 1570, 1571, 1572, 1573, 1574, 1578, 1579. Please see my code attached. In the code, I just mentioned DXPRIME only but I also want DXLSF, DXF2-DXF13 if they have 157, 1571, ...1579.

Also is there a way I can add an array statement for SELECT statement to include DXF2-DXF13 rather than stating each variable separately?

Thank you so much!

SMS

proc sql;
connect to sqlsvr as cdw (DATAsrc=ORD_201705D &SQL_OPTIMAL.);
create table PaCa.Inpat_Cases as
select * from connection to cdw (
SELECT [SCRSSN]
       , [DXPRIME]
	   ,[DXLSF]
	   , [DXF2]
, [DXF13]
from [ORD_201705D]. [Src]. [MedSAS_Inp_PM]
where DXPRIME in ('157', '1570', '1571', '1572')
);
DISCONNECT FROM cdw;
QUIT;

[Tom]

The basic answer is no.  You might ask your DBA if the SQL Server SQL supports some type of variable lists, but you cannot use them in the SAS side of the query.

But you could use SAS to quickly help you generate the list of all of the variables in that table you are querying by using the FEEDBACK option on the PROC SQL statement.  So something like this with an obviously false WHERE condition so that no actual data is moved, but all of the variables are created.

proc sql feedback;
connect to sqlsvr as cdw (DATAsrc=ORD_201705D &SQL_OPTIMAL.);
create table test1 as
select * from connection to cdw (
select *
from [ORD_201705D].[Src].[MedSAS_Inp_PM]
where 1=0
);
disconnect from cdw;
quit;

Then you could copy the names from the note in the log and use it to generate the query you want, although you might need to add those silly square brackets if the SQL server really requires them.

[ChrisNZ]

Since you use explicit pass-thru, this is a TSQL question really.

In the SAS world, you can use this:

data HAVE;
  DXLSF ='aaa'; DXF2='bbb'; output;
  DXLSF ='ccc'; DXF2='ddd'; output;
proc sql;
  select *
  from HAVE
  where find(catx(',',DXLSF,DXF2),'bbb');
  select *
  from HAVE
  where find(DXLSF||DXF2,'bbb');
quit;

Use option

options sastrace=',,,d' sastraceloc=saslog nostsuffix dbidirectexec;

to see what SAS manages to pass to SQL in implicit passthru.