Desktop productivity for business analysts and programmers

Question about data and creating a table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Question about data and creating a table

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?


Accepted Solutions
Solution
‎05-17-2017 03:35 PM
Super User
Posts: 5,359

Re: Question about data and creating a table

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


All Replies
Super User
Posts: 19,052

Re: Question about data and creating a table

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.

Community Manager
Posts: 2,887

Re: Question about data and creating a table

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.

Super User
Posts: 5,359

Re: Question about data and creating a table

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)

Occasional Contributor
Posts: 19

Re: Question about data and creating a table

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.

Solution
‎05-17-2017 03:35 PM
Super User
Posts: 5,359

Re: Question about data and creating a table

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.

Occasional Contributor
Posts: 19

Re: Question about data and creating a table

got it thanks!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 168 views
  • 1 like
  • 4 in conversation