IF _N_=1 Then SET

[Ronein]

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;

[ChrisNZ]

Test first.TYPE instead of _N_

[mkeintz · Accepted solution]

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;

[quickbluefish]

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;

[Tom]

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;