topic Re: sas in SAS Programming

PROC SQL not producing the desired output

manya92 — Thu, 05 Jul 2018 19:49:44 GMT

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*/

Re: sas

PaigeMiller — Thu, 05 Jul 2018 18:00:59 GMT

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".

Re: sas

Astounding — Thu, 05 Jul 2018 18:34:17 GMT

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..............................

Re: sas

PGStats — Thu, 05 Jul 2018 18:48:16 GMT

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