topic Re: Updating dataset or Merging by variables using SQL in SAS Programming

Updating dataset or Merging by variables using SQL

sas_apprenant — Sat, 18 Jul 2020 19:03:40 GMT

I tried to merge both datasets based on the variables ClassVal0 and param. For some reason though the coding I used is not working. First I tried to update based using this code below before merging but it said SQL Update: ERROR: Subquery evaluated to more than one row.

proc sql;
	update bout.allm as a
	set ClassVal0 = (select (ClassVal0) 
	from nhns.chdsimplogtab as b 
	where a.Param = b.Param
	group by b.Param);
quit;

Then I just tried to combine them together based on this code below and it just didn't work out with values mismatched.

proc sql;
create table mamlogistictable as
select a.*, b.M_OR_CI, b.M_p_value, b.M_g_p_value
from btab.lnelmam_simplogtab a
left join bout.allm b
on a.ClassVal0 = b.ClassVal0 
and a.Param = b.Param
order by Param;
quit;

What am I doing wrong? I'd appreciate the explanation.

Thank you

Re: Updating dataset or Merging by variables using SQL

sas_apprenant — Sat, 18 Jul 2020 17:20:15 GMT

It is "on" not "bon". The error was made while posting it.

Re: Updating dataset or Merging by variables using SQL

Tom — Sat, 18 Jul 2020 17:37:01 GMT

Please post data as text, not photographs. Limit to enough variables and observations to demonstrate the issue.

As the error says to use a subquery to update a value with the UPDATE statement in SQL the subquery must return only one value. Which of the many values to you want to use? Is there some way to pick the right one? Perhaps you want the min or max value?

Re: Updating dataset or Merging by variables using SQL

PGStats — Sat, 18 Jul 2020 18:56:00 GMT

Based on the *pictures* of the data, the join should work. Try replacing the left join with an inner join. Please give a more detailed description of the missmatch that you obtain.

Re: Updating dataset or Merging by variables using SQL

sas_apprenant — Sat, 18 Jul 2020 19:09:59 GMT

I tried the inner join and the output does not work with what I want. Essentially, I want to add the output of my adjusted regression analysis to simple regression with the adjusted odd ratios, CIs, and global p-value. Using inner join, I got the attached result (file added). Also, I added the files from the original.

Re: Updating dataset or Merging by variables using SQL

PGStats — Sat, 18 Jul 2020 20:57:25 GMT

I think you were arleady getting the right resuIts, but the ordering was confusing. I get decent results with :

proc sql;
create table mamlogistictable as
select  
    a.*, 
    b.M_OR_CI, 
    b.M_p_value, 
    b.M_g_p_value
from 
    sasforum.lnelmam_simplogtab a left join 
    sasforum.allm b on a.ClassVal0 = b.ClassVal0 and a.Param = b.Param
order by f_ord;
quit;

Replace sasforum with your own library names.

Re: Updating dataset or Merging by variables using SQL

sas_apprenant — Mon, 20 Jul 2020 17:48:49 GMT

I realized I was on the right track and figured it out. Thank you for the guidance.

I appreciate it!