DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 131
Accepted Solution

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

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 ...


Accepted Solutions
Solution
‎09-10-2017 11:08 AM
Super User
Posts: 24,004

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

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. 

View solution in original post


All Replies
Solution
‎09-10-2017 11:08 AM
Super User
Posts: 24,004

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

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. 

Respected Advisor
Posts: 4,795

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

@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;
Frequent Contributor
Frequent Contributor
Posts: 131

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

Thank you, that works.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 391 views
  • 2 likes
  • 3 in conversation