topic Re: PROCS SQL delete rows with missing values in SAS Programming

PROCS SQL delete rows with missing values

matoma — Thu, 13 Feb 2020 18:34:24 GMT

I'm trying to combine these tables with PROC SQL and then delete any rows with missing data but I can't figure it out. Heres what I have.

Re: PROCS SQL delete rows with missing values

Reeza — Thu, 13 Feb 2020 18:46:37 GMT

What defines 'missing data'?
Please post your code as text, not as an image. If I need to modify or add on to your code this means you're asking us to type it all out from scratch when it's infinitely easier and faster to copy/paste and edit your code.

In general, you can use MISSING() to check if a value is missing.

Re: PROCS SQL delete rows with missing values

Krueger — Thu, 13 Feb 2020 18:55:09 GMT

SQL is very different when it comes to merging compared to SAS. You have to actually use the JOIN Statement for each individual table.

Below should be a working version of what your trying to do. Note your missing a semi-colon at the end of your where statement which in this case wouldn't fix errors but would still prevent this from running even if your code was correct.

For future reference: if you can use the {i} in your posts and plug your code in there it allows us to copy it instead of having to re-type it out. Another thing...when your running this in SAS you should be getting errors in your LOG. Copying these errors and providing is also very helpful, as well as for yourself because you can usually search the error and find a solution yourself.

proc sql;
create table hw2.merge as
select *
  from hw2.anthropometrics a
  join hw2.demographics as b on a.patient_id = b.patient_id
  join hw2.bloodpressure as c on a.patient_id = c.patient_id
  join hw2.allids as d on a.patient_id = c.patient_id
;
quit;

Compared to a merge in a data step:

data want;
  merge hw2.anthropometrics hw2.demographis hw2.bloodpressure hw2.allids;
  by patient_id;
run;

Re: PROCS SQL delete rows with missing values

matoma — Thu, 13 Feb 2020 18:51:46 GMT

I'm trying to merge these tables together and then delete any row with missing data from any variable. Heres what I have. I can't find any way to refer to all variables.

proc sql;

create table hw2.merge as

select *

from hw2.anthropometrics as a, hw2.demographics as b, hw2.bloodpressure as c, hw2.allids as d

where a.patient_id=b.patient_id=c.patient_id=d.patient_id;

quit;

Re: PROCS SQL delete rows with missing values

PGStats — Thu, 13 Feb 2020 20:31:08 GMT

In SAS/SQL you can use the CMISS function to count missing values ( WHERE CMISS( A, B, C, X, ... ) = 0, for example), but there is no shortcut syntax to specify variable lists. You will have to list all your variables explicitly as arguments of the CMISS function.

Re: PROCS SQL delete rows with missing values

Reeza — Thu, 13 Feb 2020 20:42:18 GMT

You can use _all_ within a data step but not a SQL query. SQL doesn't support variable shortcuts or lists.