topic Re: IF THEN DO, proc sql in New SAS User

IF THEN DO, proc sql

bkq32 — Fri, 03 Jan 2020 03:15:26 GMT

I'm trying to learn SQL and was wondering how to recreate the second DATA step using PROC SQL.

data have;
 input id $1. dx_mo dx_dy $2. dx_yr $4.;
 cards;
1 99 99 1994
2 02 05 1994
3 01 09 1990
4 03 03 1991
5 04 99 1993
;
run;

/* Replace missing date components with valid values */
data want;
 set have;
 if dx_mo="99" then do;
  dx_mo="07";
  dx_dy="01";
 end;
  else if dx_dy="99" then dx_dy="15";
run;

I started with the following code but got stuck here:

proc sql;
CREATE TABLE want
AS SELECT CASE
           WHEN dx_mo="99" THEN "07"
	    ELSE dx_mo
          END AS dx_mo,

          CASE
           WHEN dx_mo="99" THEN "01"
	    ELSE dx_dy
	  END AS dx_dy
FROM have;

Re: IF THEN DO, proc sql

Patrick — Fri, 03 Jan 2020 03:42:47 GMT

Something like below should return the same result.

proc sql;
  create table want as
    select 
      case
        when dx_mo="99" then "07"
        else dx_mo
      end 
      as dx_mo,
      case
        when dx_mo="99" then "01"
        when dx_dy="99" then "15"
        else dx_dy
      end 
      as dx_dy
    from have
  ;
quit;

With the sample data you've posted you'll get in the data step the following warning:

NOTE: Character values have been converted to numeric values

You'll observe that SQL is less tolerant here and that you need to provide the correct data types.

Re: IF THEN DO, proc sql

bkq32 — Fri, 03 Jan 2020 03:53:28 GMT

@Patrick Thank you!