DATA Step, Macro, Functions and more

PROC SQL not producing the desired output

Reply
Contributor
Posts: 47

PROC SQL not producing the desired output

[ Edited ]

I wrote this code, to select all information for my study sample from the big marketscan dataset, But i want to print the enrolid from _01_hiv_tb2 but it is not being printed. What can be a good solution to this issue ?

proc sql;
	create table derived._01_itable as
	select  dx1, dx2,dx3,dx4,dx5,dx6,dx7,dx8,dx9,dx10,dx11,dx12,dx13,dx14 
	from dmk_scan.ci_2011 
	where enrolid in (select enrolid from _01_hiv_tb2) 
	union
	select   dx1, dx2,dx3,dx4,dx5,dx6,dx7,dx8,dx9,dx10,dx11,dx12,dx13,dx14 
	from dmk_scan.ci_2012 
	where enrolid in (select enrolid from _01_hiv_tb2)
	union
	select   dx1, dx2,dx3,dx4,dx5,dx6,dx7,dx8,dx9,dx10,dx11,dx12,dx13,dx14
	from dmk_scan.ci_2013 
	where enrolid in (select enrolid from _01_hiv_tb2)
	union
	select   dx1, dx2,dx3,dx4,dx5,dx6,dx7,dx8,dx9,dx10,dx11,dx12,dx13,dx14
	from dmk_scan.ci_2014 
	where enrolid in (select enrolid from _01_hiv_tb2)
	union
	select   dx1, dx2,dx3,dx4,dx5,dx6,dx7,dx8,dx9,dx10,dx11,dx12,dx13,dx14 
	from dmk_scan.ri_2011 
	where enrolid in (select enrolid from _01_hiv_tb2)
	union
	select   dx1, dx2,dx3,dx4,dx5,dx6,dx7,dx8,dx9,dx10,dx11,dx12,dx13,dx14 
	from dmk_scan.ri_2012
	where enrolid in (select enrolid from _01_hiv_tb2)
	union
	select   dx1, dx2,dx3,dx4,dx5,dx6,dx7,dx8,dx9,dx10,dx11,dx12,dx13,dx14 
	from dmk_scan.ri_2013
	where enrolid in (select enrolid from _01_hiv_tb2)
	union
	select   dx1, dx2,dx3,dx4,dx5,dx6,dx7,dx8,dx9,dx10,dx11,dx12,dx13,dx14
	from dmk_scan.ri_2014 ;
quit ;/*697321*/
Respected Advisor
Posts: 2,989

Re: sas

I don't see anything in your code that would result in information being printed.

 

By the way, it is not time to ask politely that you provide meaningful titles for your posts. "SAS" (in lower case) is not a meaningful title. The title of your previous post "The SAS Supervisor" is not a meaningful title.

 

For this particular post, a meaningful title could be something like "PROC SQL not producing the desired output".

--
Paige Miller
Super User
Posts: 6,754

Re: sas

ENROLID is not part of the table you are creating.  If you want it to exist in that data set, it's not enough to mention ENROLID in a WHERE clause.  You have to actually add it to the list of fields you select:

 

select enrolid, dx1, dx2, dx3..............................

Esteemed Advisor
Posts: 5,523

Re: sas

A side note: You could replace UNION with UNION ALL to speed things up.

PG
Ask a Question
Discussion stats
  • 3 replies
  • 53 views
  • 0 likes
  • 4 in conversation