How to - Multiply Same Values for Another Specific Datas?

[ertr]

Hello everybody,

I would like to ask a short questions to you. I have a sample data set as below;

Data Have;
Length Variabale1 $ 32 Variable2 $ 32;
Infile Datalines Missover;
Input Variabale1 Variable2;
Datalines;
AA AZA
AA AZB
AA AZC
AB AZA
AB AZB
AB AZC
Run;

And I have values for Variable1 such as AC,AD, AE. At the end of the day, I want to multiply datas for these values. I shared my desired output as below;

I want to do it sorted, I mean exact format with the output

Can somebody help me about it, please?

Thanks

[Astounding]

It's actually easier if you keep the VARIABLE1 and VARIABLE2 values separate to start.  GIven that you have them together, let's separate them and reassemble the pieces:

proc sql;

create table var1_values as select distinct(variable1) from have;

create table var2_values as select distinct(variable2) from have;

create table want as select * from var1_values, var2_values order by variable1, variable2;

quit;

[ertr]

Thank you but your code gives an error. My real purpose append new values under my have data set which you can see  also desired data set. Do you have any other ideas for this?

Thanks,

[Astounding]

I can't see the error that you are looking at.  You might have to post the log.

Do you have just a small list of new values to add to your existing data?  Is that list stored as a data set?  It's really not clear what data is available and where values like "AD" and "AE" are coming from.

[novinosrin]

Data Have;
Length Variabale1 $ 32 Variable2 $ 32;
Infile Datalines Missover;
Input Variabale1 Variable2;
Datalines;
AA AZA
AA AZB
AA AZC
AB AZA
AB AZB
AB AZC
Run;

proc sql;
create table want as
select  *
from
(select distinct Variabale1 from have),(select distinct Variable2 from have)
order by Variabale1;
quit;

[novinosrin]

Test this against proc sql cartesian for performance and let me know

Data Have;
Length Variabale1 $ 32 Variable2 $ 32;
Infile Datalines Missover;
Input Variabale1 Variable2;
Datalines;
AA AZA
AA AZB
AA AZC
AB AZA
AB AZB
AB AZC
Run;

proc sql ;                                                              
  create index Variabale1 on have (Variabale1) ;                      
quit ;
data want ;
if _n_=1 then do;
   dcl hash H (dataset:'have(keep=Variable2)',ordered: "A") ;
   h.definekey  ("Variable2") ;
   h.definedone () ;
   dcl hiter hh('h');
   end;
set have end=lr;
by Variabale1;
if first.Variabale1;
do while(hh.next()=0);
output;
end;
run;

[ertr]

I do not think your both codes gives my desired output, are you also tried in your environment? I also do not prefer to use Hash codes.Thank you for your help but do you have any other suggestions?

Thanks

[novinosrin]

Can you post a sample of you what you WANT as output for the input HAVE sample you posted at the top plz?

[Patrick]

@ertr 

You still haven't told us where a value like 'AE' comes from as it's nowhere in your source data. It also would be kind of you if you don't just reply to people that the code proposed errors out but if you'd actually would post the relevant log section as well so people can understand why things didn't work for you.

Assuming you just want to generate the data below an option: 

data sample(drop=_:);

  length var1 $2 var2 $3;
  _start=rank('A');
  _stop=rank('Z');

  do _i=_start to _stop;
    var1='A'||byte(_i);
    do _j=_start to _start+2;
      var2='AZ'||byte(_j);
      output;
    end;
  end;

  stop;
run;

[ertr]

@Patrick , @novinosrin 

Hello, Sorry, If I missed something to tell you. Those values coming from nowhere.  I mean nowhere such as Excel, data set and etc. We just know it. I found a workaround solution as below, do you have an another idea for this 

Data Have;
Length Variabale1 $ 32 Variable2 $ 32;
Infile Datalines Missover;
Input Variabale1 Variable2;
Datalines;
AA AZA
AA AZB
AA AZC
;
Run;
data Want;
set Have(where=(Variabale1="AA"));
do Variabale1="AB", "AC", "AD", "AE";
output;
end;
run;
proc sql noprint;
insert into Have select * from Want;
quit;
Proc Sort DATA=WANT;
BY Variabale1 Variable2;
Run;

This one meet my expectation but I want shorter method for this

[Patrick]

@ertr 

Doesn't the code I've posted give you what you're after?

Just replace line 

 _stop=rank('Z');

with

 _stop=rank('E');