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;
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;
Test first.TYPE instead of _N_
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;
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;
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;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.