topic Re: Question about data and creating a table in SAS Enterprise Guide

Question about data and creating a table

Rsadiq1 — Wed, 17 May 2017 18:51:28 GMT

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?

Re: Question about data and creating a table

Reeza — Wed, 17 May 2017 18:57:49 GMT

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.

Re: Question about data and creating a table

ChrisHemedinger — Wed, 17 May 2017 18:59:45 GMT

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.

Re: Question about data and creating a table

Astounding — Wed, 17 May 2017 19:01:22 GMT

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)

Re: Question about data and creating a table

Rsadiq1 — Wed, 17 May 2017 19:18:04 GMT

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.

Re: Question about data and creating a table

Astounding — Wed, 17 May 2017 19:28:21 GMT

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.

Re: Question about data and creating a table

Rsadiq1 — Wed, 17 May 2017 19:34:56 GMT

got it thanks!