Skip to content
Advertisement

Wrong data type notification between in operator in SAS

When I run the following code in SAS, I was told that the data type between ‘not in’ were different and can’t procede to run. But I’ve already used ‘input’ to convert the data type, so I don’t know what’s going wrong.

NOTE: DAT and EGDAT are not from the same table and have different original data type.

 case when (input(DAT, yymmdd10.)) NOT IN 
                    (SELECT input(EGDAT, yymmdd10.) FROM SOURCE.EG001 WHERE VISIT IN 
                        ('SCREENING' , 'CYCLE1_DAY1' , ...))

Advertisement

Answer

Either the data columns are not as you presume, or there is some other part of the query that is causing the message.

Example:

The SQL code similar to yours and there are no errors.

data claims(label="Simulated dates");
  length patid 8 DAT $8;

  call streaminit(20200423);

  do patid = 1 to 100;
  do _n_ = '01jan2020'd to '23apr2020'd;
    DAT = put (_n_, yymmddn8.);
    if rand ('uniform') < 0.05 then output;
  end;
  end;
run;

data visits(label="Simulated visit reasons");
  length patid 8 EGDAT $22;
  set claims(rename=dat=egdat);

  call streaminit(20200423);
  _n_ = rand('uniform');
  length visit $25;
  select;
    when (_n_ < 0.15) visit = 'SCREENING';
    when (_n_ < 0.30) visit = 'CANCELLED';
    when (_n_ < 0.45) visit = 'CYCLE1_DAY1';
    when (_n_ < 0.55) visit = 'ACHES';
    when (_n_ < 0.75) visit = 'PAINS';
    when (_n_ < 0.95) visit = 'CYCLE1_DAY2';
    otherwise visit = 'PLEASANT';
  end;
  output;
run;

proc sql; 
  create table want as
  select 
    claims.patid 
  , claims.DAT
  , case
      when input(DAT, yymmdd10.) not in 
      ( select input(egdat, yymmdd10.) 
        from visits
        where visits.patid = claims.patid
          and visit in ('SCREENING', 'CYCLE1_DAY1', 'CYCLE2_DAY2')
      ) 
      then 'NOT A SCREENING CLAIM'
      else 'SEEMS LIKE A SCREENING CLAIM'
    end as
      visit_classification
  from claims
  order by patid, dat
  ;
quit;
----- LOG -----
NOTE: Table WORK.WANT created, with 574 rows and 3 columns.

491  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.16 seconds
      cpu time            0.01 seconds

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement