topic Re: IF _N_=1 Then SET in SAS Programming

IF _N_=1 Then SET

Ronein — Wed, 19 Mar 2025 05:33:25 GMT

Hello

I want to add values from one data set into another data set.

I want to use IF _N_=1 Then set statement.

Example1 is working 100%

In Example2 I dont get desired results.

IS there a way to get the desired results in Example2 Via IF then set statement?

I want to add average values by type

/**ex1**/
proc sql;
create table cars_a as
select mean(invoice) as AVG_Invoice
from sashelp.cars
;
quit;

Data Want;
set sashelp.cars;
If _N_=1 then set cars_a;
Run;

/**ex2**/
proc sql;
create table tbl1 as
select Type,
      mean(invoice) as AVG_Invoice
from sashelp.cars
group by Type
order by Type
;
quit;

proc sort data=sashelp.cars out=cars;by type;Run;
Data Want;
set cars;
by Type;
If _N_=1 then set tbl1;
Run;

Re: IF _N_=1 Then SET

ChrisNZ — Wed, 19 Mar 2025 05:50:13 GMT

Test first.TYPE instead of _N_

Re: IF _N_=1 Then SET

mkeintz — Wed, 19 Mar 2025 17:21:58 GMT

You can use if "first.type then set type;" per @ChrisNZ's suggestion. That would be the most similar to your other code solution.

Or you can use the MERGE statement, as in:

proc sort data=sashelp.cars out=cars;
  by type;
run;

data want;
  merge cars tbl1;
  by type;
run;

This many-to-one merge process is probably the most common use of the MERGE statement (accompanied by the BY statement).

If you don't like the idea of being required to sort sashelp.cars by TYPE, then you could load dataset TBL1 into a hash lookup table:

data want;
  set sashelp.cars tbl1 (obs=0);
  if _n_=1 then do;
    declare hash h (dataset:'tbl1');
      h.definekey('type');
      h.definedata(all:'Y');
      h.definedone();
  end;
  if h.find()^=0 then call missing(avg_invoice);
run;

Re: IF _N_=1 Then SET

quickbluefish — Wed, 19 Mar 2025 17:47:55 GMT

If you want to do this with SQL (as in your Ex. #2), you can do this:

proc sql;
create table tbl1 as
select a.*, b.avg_invoice
from
  sashelp.cars A
  inner join
  (select type, mean(invoice) as avg_invoice from sashelp.cars group by type) B
  on a.type=b.type;
quit;

Re: IF _N_=1 Then SET

Tom — Wed, 19 Mar 2025 18:38:10 GMT

If you have a BY variable then you don't need to use that trick with the conditionally executed SET statement.

Just use MERGE.

data want;
  merge cars tbl1;
  by type;
run;