Hi Team,
I have two tables.
Table 1:
Product Bud_1988 Bud_1999
X 0 0
Y 0 0
Z 0 0
Table 2:
Product Achvt_1988 Achvt_1999
X 0 0
Z 0 0
Required Output table:
Product Var 1988 1999
X Bud 0 0
X Achvt 0 0
Y Bud 0 0
Z Bud 0 0
Z Achvt 0 0
This should be done in Single Data Step(without Proc Transpose).
Can some one Please guide me
Thanks
First piece of guidance: refuse to do it. Why would someone who doesn't know how to accomplish the task give you the task and then tell you what tools you can use and what tools you can't use?
Second piece of guidance: the incoming tables are a horrible way to store data. You can see the hoops they force you to jump through when you want to use it. The table you ask for only partially fixes the problem. You would be better off creating a different form of output and learning how to program with data in this format:
Product Brand Year Quantity
X Activt 1988 0
X Activt 1999 0
Z Activt 1988 0
Z Activt 1999 0
X Bud 1988 0
X Bud 1999 0
Y Bud 1988 0
Y Bud 1999 0
Z Bud 1988 0
Z Bud 1999 0
Of course you don't always have control over all these factors, but it's a better direction than the one you are shooting for now.
Actually, sounds like an interesting interview question.
If you can add one record to Table2, the needed datastep is fairly simple:
data table1; input Product $ Bud_1988 Bud_1999; cards; X 11 12 Y 13 14 Z 15 16 ; data table2; input Product $ Achvt_1988 Achvt_1999; cards; X 21 22 Y . . Z 23 24 ; data want (keep=Product var _1988 _1999); set table1; set table2; by Product; array vars(*) Bud_1988 Bud_1999 Achvt_1988 Achvt_1999; length var $10; do i=1 to dim(vars); call missing(var); if not missing(vars(i)) then do; var=scan(vname(vars(i)),1,'_'); if scan(vname(vars(i)),2,'_') eq '1988' then _1988=vars(i); else _1999=vars(i); end; if not mod(i,2) and not missing(var) then output; end; run;
Art, CEO, AnalystFinder.com
Hi:
You could also do it with a merge and array processing without altering the input files.
Cynthia
data table1;
infile datalines;
input Product $ Bud_1988 Bud_1999;
return;
datalines;
X 0 0
Y 0 0
Z 0 0
;
run;
data table2;
infile datalines;
input Product $ Achvt_1988 Achvt_1999;
return;
datalines;
X 0 0
Z 0 0
;
run;
data long(keep=product vartype yr1988 yr1999);
length vartype $5;
merge table1(in=t1)
table2(in=t2);
by product;
array years(*) Bud_1988 Bud_1999 Achvt_1988 Achvt_1999;
do i=1 to dim(years);
call missing(vartype);
vartype=scan(vname(years(i)),1,'_');
if scan(vname(years(i)),2,'_') eq '1988' then yr1988=years(i);
else yr1999=years(i);
if mod(i,2) = 0 and
(years(i) gt . or years(i-1) gt .) then output;
end;
run;
proc print data=long;
var product vartype yr1988 yr1999;
title 'long';
run;
data table1;
infile datalines;
input Product $ Bud_1988 Bud_1999;
return;
datalines;
X 0 0
Y 0 0
Z 0 0
;
run;
data table2;
infile datalines;
input Product $ Achvt_1988 Achvt_1999;
return;
datalines;
X 0 0
Z 0 0
;
run;
data want;
set table1 table2;
by product;
array t(*) _numeric_;
array b(*) Bud_1988 Bud_1999;
if first.product then var=scan(vname(t(1)),1,'_');
else var=scan(vname(t(3)),1,'_');
if sum(of b(*))=. then do _n_=1 to dim(b);
b(_n_)=coalesce(of _numeric_);
end;
rename Bud_1988=_1988 Bud_1999=_1999;
drop Achvt_:;
run;
+1 for recognizing this problem is really an interleave and not a merge.
Hi OP @TejaSurapaneni Should your interviewer have an ego complex personality
data table1;
infile datalines;
input Product $ Bud_1988 Bud_1999;
return;
datalines;
X 0 0
Y 0 0
Z 0 0
;
run;
data table2;
infile datalines;
input Product $ Achvt_1988 Achvt_1999;
return;
datalines;
X 0 0
Z 0 0
;
run;
data want;
if _n_=1 then do;
if 0 then set table2;
dcl hash h(dataset:'table2', multidata: 'y');
h.definekey('product');
h.definedata('Achvt_1988', 'Achvt_1999');
h.definedone();
end;
set table1;
var=scan(vname(Bud_1988),1,'_');
output;
do while(h.do_over(key:product) eq 0);
var=scan(vname(Achvt_1988),1,'_');
output;
end;
rename Bud_1988=_1988 Bud_1999=_1999;
drop Achvt_:;
run;
, show off with hash although not recommended for production environment:
Doesn't look like a transpose problem at all.
You are just renaming the existing variables.
data want ;
length product $8 var $32 _1988 _1999 8 ;
set table1 (in=in1 rename=(Bud_1988=_1988 Bud_1999=_1999))
table2 (in=in2 rename=(Achvt_1988=_1988 Achvt_1999=_1999))
;
by product ;
if in1 then var='Bud';
else var='Achvt';
run;
Obs product var _1988 _1999 1 X Bud 0 0 2 X Achvt 0 0 3 Y Bud 0 0 4 Z Bud 0 0 5 Z Achvt 0 0
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: