topic Re: How to - Multiply Same Values for Another Specific Datas? in SAS Programming

How to - Multiply Same Values for Another Specific Datas?

ertr — Fri, 01 Mar 2019 17:51:04 GMT

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

Re: How to - Multiply Same Values for Another Specific Datas?

Astounding — Fri, 01 Mar 2019 18:06:07 GMT

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;

Re: How to - Multiply Same Values for Another Specific Datas?

novinosrin — Fri, 01 Mar 2019 18:03:09 GMT

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;

Re: How to - Multiply Same Values for Another Specific Datas?

novinosrin — Fri, 01 Mar 2019 19:46:24 GMT

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;

Re: How to - Multiply Same Values for Another Specific Datas?

ertr — Fri, 01 Mar 2019 22:09:03 GMT

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

Re: How to - Multiply Same Values for Another Specific Datas?

ertr — Fri, 01 Mar 2019 22:11:43 GMT

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,

Re: How to - Multiply Same Values for Another Specific Datas?

Astounding — Fri, 01 Mar 2019 22:17:08 GMT

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.

Re: How to - Multiply Same Values for Another Specific Datas?

novinosrin — Fri, 01 Mar 2019 22:29:30 GMT

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

Re: How to - Multiply Same Values for Another Specific Datas?

Patrick — Fri, 01 Mar 2019 22:50:02 GMT

@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;

Re: How to - Multiply Same Values for Another Specific Datas?

ertr — Fri, 01 Mar 2019 23:57:18 GMT

@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

Re: How to - Multiply Same Values for Another Specific Datas?

Patrick — Sat, 02 Mar 2019 00:10:09 GMT

@ertr

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

Just replace line

 _stop=rank('Z');

with

 _stop=rank('E');