topic Re: Data Transpose in SAS Programming

Data Transpose

TejaSurapaneni — Sat, 24 Mar 2018 13:23:17 GMT

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

Re: Data Transpose

Astounding — Sat, 24 Mar 2018 13:52:11 GMT

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.

Re: Data Transpose

Cynthia_sas — Sat, 24 Mar 2018 14:20:41 GMT

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

Re: Data Transpose

art297 — Sat, 24 Mar 2018 16:08:15 GMT

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

Re: Data Transpose

Cynthia_sas — Sat, 24 Mar 2018 17:10:44 GMT

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;

Re: Data Transpose

novinosrin — Sat, 24 Mar 2018 18:05:38 GMT

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;

Re: Data Transpose

Tom — Sat, 24 Mar 2018 18:14:23 GMT

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

Re: Data Transpose

novinosrin — Sat, 24 Mar 2018 18:39:00 GMT

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:

Re: Data Transpose

Tom — Sat, 24 Mar 2018 19:45:02 GMT

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

Re: Data Transpose

Cynthia_sas — Sat, 24 Mar 2018 23:53:10 GMT

That is more elegant!

Cynthia