BookmarkSubscribeRSS Feed
manya92
Fluorite | Level 6

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*/
3 REPLIES 3
PaigeMiller
Diamond | Level 26

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
Astounding
PROC Star

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

PGStats
Opal | Level 21

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

PG

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
  • 3 replies
  • 731 views
  • 0 likes
  • 4 in conversation