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?
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.
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.
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.
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)
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.
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.
got it thanks!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.