BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Rsadiq1
Calcite | Level 5

Hello

 

I ran this query

 

Proc sql ;
 Create table sasdata.hlth_lc as
 Select
  c.Patient_ID
 ,c.State
 ,c.Zip_code
 ,c.Gender
 ,c.survey_date
 ,c.score
 ,c.Q1
 ,c.Q2
 ,c.Q3
 ,c.Q4
 ,c.Q5
 ,c.Q6
 ,c.Q7
 ,c.Q10
 ,c.Q19
 ,c.Q23
 From Health.last_contact_yr2016 as c
 where put(c.Q3,2.) in ('3','4','5')
 and  put(c.Q4,2.) ='3'
 and put(c.Q19,2.)='3'
 and put(c.Q10,2.)='1'
 and put(c.Q23,2.) in ('3','4','5')
 ;
run;

 

My libnames are correctly defined, and everything, but for some reason it says generated 0 rows and 16 columns, my data is correct and is there but for some reason my table with my specific variables are not being created/its not reading the data?

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

You created the error in the first place, by adding quotes around the numeric values.  That's what forced you into using the PUT function.  Just get rid of the quotes and the PUT function at the same time.

View solution in original post

6 REPLIES 6
Reeza
Super User

Remove your WHERE clause and add the conditions back one at a time. 

Then you can figure out why you're not getting any records. 

 

Additionally, this assumes your code was correct. Include your LOG to show that the code does not have errors as written.

ChrisHemedinger
Community Manager

Are the Qn variables numeric types? If so, your WHERE clause is unnecessarily complicated.

 

You can quickly test some of your conditions in the Where field in the data viewer.

SAS Hackathon registration is open! Build your skills. Make connections. Enjoy creative freedom. Maybe change the world.
Astounding
PROC Star

The problem does indeed lie in the WHERE clause:

 


 where put(c.Q3,2.) in ('3','4','5')
 and  put(c.Q4,2.) ='3'
 and put(c.Q19,2.)='3'
 and put(c.Q10,2.)='1'
 and put(c.Q23,2.) in ('3','4','5')

 

By using the 2. format, you will never find a match.  The 2. format gives you a leading blank followed by a digit, which will never match values that have no leading blanks.  Why not get rid of the PUT function entirely?

 


 where c.Q3 in (3, 4, 5)
 and  c.Q4=3
 and c.Q19=3
 and c.Q10=1
 and c.Q23 in (3, 4, 5)

Rsadiq1
Calcite | Level 5

I tried without the put function but it said the data types were not the same so I added the put function for that reason, which fixed the error.

Astounding
PROC Star

You created the error in the first place, by adding quotes around the numeric values.  That's what forced you into using the PUT function.  Just get rid of the quotes and the PUT function at the same time.

Rsadiq1
Calcite | Level 5

got it thanks!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 1153 views
  • 1 like
  • 4 in conversation