topic Re: COALESCE statement in proc sql in SAS Programming

COALESCE statement in proc sql

SASdevAnneMarie — Fri, 06 Oct 2023 15:48:14 GMT

Hello Experts,

I would like to join conditionally the data : if ctant.ctant_pere is doesn't missing I'm using this key, in other way I'm using ctant.contrac.

But my code is doesn't work, could you tell me where is the problem, please?

proc sql;
	create table date_naiss
		as select 
			pers.D_NAISS, sousc.no_pol, coalesce(ctant.ctant_pere,ctant.contrac) as COAL
		from dossier sousc, 
			dossier2 ctant, personne pers where 
			and sousc.contr = calculated COAL
			and ctant.pers = pers.pers;
quit;

Thank you very much!

Re: COALESCE statement in proc sql

maguiremq — Fri, 06 Oct 2023 15:54:04 GMT

Are you getting an error?

Code is untested, but you can just move that COALESCE to the join.

PROC SQL;
CREATE TABLE date_naiss AS
SELECT
  pers.date_naiss
  , sousc.no_pol
  , COALESCE(ctant.ctant_pere, ctant.contrac) AS coal
FROM
  dossier sousc
  , dossier2 ctant
  , personne pers
WHERE
 sousc.countr = COALESCE(ctant.pere, ctant.contrac)
 AND ctant.pers = pers.pers
;
QUIT;

Again, I think that works but I can't tell without data.

I'd assume the calculated doesn't work in the join.

Re: COALESCE statement in proc sql

SASdevAnneMarie — Fri, 06 Oct 2023 16:21:38 GMT

Thank you, I already tried it, but seems thant COALESCE statement doesn't work with join.

Re: COALESCE statement in proc sql

ballardw — Fri, 06 Oct 2023 16:46:27 GMT

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Re: COALESCE statement in proc sql

Tom — Sat, 07 Oct 2023 02:59:00 GMT

Are you sure the issue is the COALESCE() function call? It seems to work fine for me

570  proc sql;
571  create table test as select a.*,coalesce(a.height,a.weight) as NEWVAR
572  from sashelp.class a, sashelp.class b
573  where b.height=calculated newvar;
NOTE: Table WORK.TEST created, with 23 rows and 6 columns.

Your WHERE clause looks invalid since it has the binary operator AND with only one input. Either remove the AND or place some other binary expression before it.

proc sql;
create table date_naiss as 
select pers.D_NAISS
     , sousc.no_pol
     , coalesce(ctant.ctant_pere,ctant.contrac) as COAL
from dossier sousc
   , dossier2 ctant
   , personne pers 
where (1=1) 
  and sousc.contr = calculated COAL
  and ctant.pers = pers.pers
;
quit;