Data Transpose

[TejaSurapaneni]

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

[Astounding]

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.

[Cynthia_sas]

Hi:
What you've been asked to do shows that they don't understand SAS. You can't have a variable name that starts with a number so "1988" and "1999" are not valid variable names, unless you move to using named literals, which is not what you show.

Cynthia

[art297]

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

[Cynthia_sas]

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;

[novinosrin]

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;

[Tom]

+1 for recognizing this problem is really an interleave and not a merge.

[novinosrin]

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:

[Tom]

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

[Cynthia_sas]

That is more elegant!

Cynthia