topic Re: how to check if a variable not exists in a dataset first then proc sql insert into table in SAS Programming

how to check if a variable not exists in a dataset first then proc sql insert into table

JHE — Sun, 10 Sep 2017 04:02:01 GMT

I want check wheather exsiting in the dataset, then preform PROC SQL insert into table.

For example check if month 2017-08 not exsiting then

Proc SQL;

Insert into table ...

Re: how to check if a variable not exists in a dataset first then proc sql insert into table

Reeza — Sun, 10 Sep 2017 05:05:24 GMT

Are you trying to check for a value in a column, if it doesn't exist insert a new row with the value?

Or are you trying to create a new variable?

If its the first look af an UPDATE statement instead.

Re: how to check if a variable not exists in a dataset first then proc sql insert into table

Patrick — Sun, 10 Sep 2017 06:53:18 GMT

@JHE

Something like below could do.

data master;
  origin='Master';
  format myDateVar date9.;

  myDateVar='01Aug2017'd;
  do i=1 to 10;
    output;
  end;
run;

data trans;
  format myDateVar date9.;
  origin='Trans';

  i=11;
  myDateVar='05Aug2017'd;
  output;

  i=12;
  myDateVar='01Sep2017'd;
  origin='Trans';
  output;
run;

%let max_month='01Jan1960'd;
proc sql noprint;
  select name into:varlist separated by ','
  from dictionary.columns where libname='WORK' and memname='MASTER' order by varnum
  ;
  select max(intnx('month',myDateVar,0,'b')) into :max_month
  from master
  ;
  insert into master
  select &varlist
  from trans
  where intnx('month',myDateVar,0,'b') > &max_month
  ;
quit;

Re: how to check if a variable not exists in a dataset first then proc sql insert into table

JHE — Sun, 10 Sep 2017 15:08:59 GMT

Thank you, that works.