BookmarkSubscribeRSS Feed
TejaSurapaneni
Lapis Lazuli | Level 10

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

 

9 REPLIES 9
Astounding
PROC Star

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
SAS Super FREQ
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
Opal | Level 21

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
SAS Super FREQ

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
Tourmaline | Level 20
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
Super User Tom
Super User

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

novinosrin
Tourmaline | Level 20

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
Super User Tom
Super User

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
SAS Super FREQ
That is more elegant!

Cynthia

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1440 views
  • 2 likes
  • 6 in conversation