BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Deva_123
Calcite | Level 5

Hi All, 

 

I have Data set below :                                                     

SateRate
Alabama0.0400
Alaska0.0000
Arizona0.0560
Arkansas0.0650
California (b)0.0725
Colorado0.0290
Connecticut0.0635
Delaware0.0000
District of Columbia0.0575
Florida0.0600
Georgia0.0400
Hawaii (c)0.0400
Idaho0.0600
Illinois0.0625
Indiana0.0700
Iowa0.0600
Kansas0.0650
Kentucky0.0600
Louisiana0.0445
Maine0.0550
Maryland0.0600
Massachusetts0.0625
Michigan0.0600
Minnesota0.0688
Mississippi0.0700
Missouri0.0423
Montana (d)0.0000

 

 

I need output as :

StateCompare_withDifference Vector
AlabamaAlaska0.0400
AlabamaArizona-0.0160
AlabamaArizona-0.0160
AlabamaArkansas-0.0250
AlabamaCalifornia (b)-0.0325
AlabamaColorado0.0110
AlabamaConnecticut-0.0235
AlabamaDelaware0.0400

 

Like this I need to compare with each state with corresponding difference Vector.

Please help me!

 

Thanks In advance!

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20
data have;
input State :$30. Rate;
infile datalines dlm=',';
datalines;
Alabama,0.0400
Alaska,0.0000
Arizona,0.0560
Arkansas,0.0650
California (b),0.0725
Colorado,0.0290
Connecticut,0.0635
Delaware,0.0000
District of Columbia,0.0575
Florida,0.0600
Georgia,0.0400
Hawaii (c),0.0400
Idaho,0.0600
Illinois,0.0625
Indiana,0.0700
Iowa,0.0600
Kansas,0.0650
Kentucky,0.0600
Louisiana,0.0445
Maine,0.0550
Maryland,0.0600
Massachusetts,0.0625
Michigan,0.0600
Minnesota,0.0688
Mississippi,0.0700
Missouri,0.0423
Montana (d),0.0000
;

data want;
   set have;
   do i=1 to n;
      set have(rename=(Rate=_Rate State=Compare_with)) point=i nobs=n;
      Diff=Rate-_Rate;
      if State ne Compare_with then output;
   end;
   keep State Compare_with Diff;
run;

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20
data have;
input State :$30. Rate;
infile datalines dlm=',';
datalines;
Alabama,0.0400
Alaska,0.0000
Arizona,0.0560
Arkansas,0.0650
California (b),0.0725
Colorado,0.0290
Connecticut,0.0635
Delaware,0.0000
District of Columbia,0.0575
Florida,0.0600
Georgia,0.0400
Hawaii (c),0.0400
Idaho,0.0600
Illinois,0.0625
Indiana,0.0700
Iowa,0.0600
Kansas,0.0650
Kentucky,0.0600
Louisiana,0.0445
Maine,0.0550
Maryland,0.0600
Massachusetts,0.0625
Michigan,0.0600
Minnesota,0.0688
Mississippi,0.0700
Missouri,0.0423
Montana (d),0.0000
;

data want;
   set have;
   do i=1 to n;
      set have(rename=(Rate=_Rate State=Compare_with)) point=i nobs=n;
      Diff=Rate-_Rate;
      if State ne Compare_with then output;
   end;
   keep State Compare_with Diff;
run;
Deva_123
Calcite | Level 5

Wow! That's so quick.. Perfect Solution!

PaigeMiller
Diamond | Level 26
data want;
     if _n_=1 then set have(obs=1 rename=(rate=original_rate));
     set have(firstobs=2 rename=(state=compare_with));
run;

I didn't add in code to do the subtraction, I figure you can do that yourself (and I'm lazy).

--
Paige Miller
Deva_123
Calcite | Level 5

Thanks a lot for your quick answer. this is perfect!

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!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 5 replies
  • 554 views
  • 0 likes
  • 3 in conversation