topic why one of the variable did not show up in the file in New SAS User

why one of the variable did not show up in the file

juliajulia — Thu, 18 Nov 2021 20:37:57 GMT

Hello everyone,

I am new to SAS SQL. somebody wrote a proc sql sas code and i just try to modify it by adding one more variables (orgname) in the code but some reasons it doesn't work. there is no any error message in the log window. so i can't figure out where has been wrong. the only one i added is the 'OrgName' but it did not show up in the output table1. the variable 'OrgName' is in the table C. I appreciate your help to take a look at the code:

proc sql;
create table output.table1 as
select distinct
a.clientreportid
,a.clientuci
,a.sexatbirthid
,2020-birthyear as Age
,a.providerid
from A

,(select distinct
clientreportid
,providerid
,case when count(*) gt 1 then 7 else raceid end as newrace /*Find multi race clients*/
from B
group by providerid, clientreportid)

,(select distinct
orgid as Providerid
,stateid
,orgName
from C
where a.providerid eq b.providerid and a.clientreportid eq b.clientreportid and a.providerid eq c.providerid
;
quit;

Re: why one of the variable did not show up in the file

PaigeMiller — Thu, 18 Nov 2021 20:41:50 GMT

This can't work at all because your parentheses are not matched.

Show us the log anyway ... all of it for this PROC SQL including the code and all NOTEs, WARNINGs and ERRORs.

Re: why one of the variable did not show up in the file

juliajulia — Thu, 18 Nov 2021 21:07:52 GMT

this is the log file:

139 proc sql;
140 create table1 as
141 select distinct
142 a.clientreportid
143 ,a.clientuci
144 ,a.sexatbirthid
145 ,2020-birthyear as Age
146 ,a.transgenderid
147 ,a.providerid
148 ,a.povertylevelpercent
149 ,a.HousingStatusId
150 ,stateid format=st.
151 ,birthyear
152 ,case
153 when ethnicityid eq 1 then 6
154 when ethnicityid ne 1 then newrace else .
155 end as racenew
156 ,case when genderid in(6,7,8) then 3
157 when genderid in(4,5) then . else genderid end as Gender3
158 ,a.ethnicityid
159 ,a.genderid
160 from A
161
162 ,(select distinct
163 clientreportid
164 ,providerid
165 ,case when count(*) gt 1 then 7 else raceid end as newrace /*Find multi race clients*/
166 from clientreportrace
167 group by providerid, clientreportid) as B
168
169 ,(select distinct
170 orgid as Providerid
171 ,stateid
172 ,orgName
173 from report) as C
174 where a.providerid eq b.providerid and a.clientreportid eq b.clientreportid and a.providerid
174! eq c.providerid
175 ;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table Table created, with 744103 rows and 14 columns.

176 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 2:38.51
cpu time 4.79 seconds

Re: why one of the variable did not show up in the file

Reeza — Thu, 18 Nov 2021 21:21:35 GMT

Which table has ORGNAME and where do you want it to be included?

Please show the working query from before you modified it, this looks to have syntax issues.

Re: why one of the variable did not show up in the file

Reeza — Fri, 19 Nov 2021 18:12:38 GMT

The query didn't add ORGNAME to the SELECT it only joined with the file.

Add it to the SELECT statement.

proc sql;
	create table1 as 
	select distinct 
	a.clientreportid
 , a.clientuci
 , a.sexatbirthid
 , 2020-birthyear as Age
 , a.transgenderid
 , a.providerid
 , a.povertylevelpercent
 , a.HousingStatusId
 , stateid format=st.
 , birthyear
 ,c.orgName
 , case when ethnicityid eq 1 then 6 when ethnicityid ne 1 then newrace else .
 end as racenew
 , case when genderid in(6, 7, 8) then 3 when genderid in(4, 5) then . else 
		genderid end as Gender3
 , a.ethnicityid
 , a.genderid 
 
 from A

 , (select distinct clientreportid
 , providerid
 , case when count(*) gt 1 then 7 else raceid end as 
		newrace /*Find multi race clients*/
		from clientreportrace group by providerid, clientreportid) as B

 , (select distinct orgid as Providerid
 , stateid
 , orgName from report) as C 
 
 where a.providerid eq b.providerid and 
		a.clientreportid eq b.clientreportid and a.providerid eq c.providerid;
quit;

Re: why one of the variable did not show up in the file

juliajulia — Fri, 19 Nov 2021 17:43:49 GMT

I see. i appreciate your help. I am still learning.😁