BookmarkSubscribeRSS Feed
sms1891
Quartz | Level 8

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;
2 REPLIES 2
Tom
Super User Tom
Super User

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
Tourmaline | Level 20

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.

 

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1290 views
  • 0 likes
  • 3 in conversation