topic Re: Holds the First observation and compare with rest of the Dataset in New SAS User

Holds the First observation and compare with rest of the Dataset

Deva_123 — Thu, 25 Jul 2019 14:39:36 GMT

Hi All,

I have Data set below :

Sate	Rate
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

I need output as :

State	Compare_with	Difference Vector
Alabama	Alaska	0.0400
Alabama	Arizona	-0.0160
Alabama	Arizona	-0.0160
Alabama	Arkansas	-0.0250
Alabama	California (b)	-0.0325
Alabama	Colorado	0.0110
Alabama	Connecticut	-0.0235
Alabama	Delaware	0.0400

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

Please help me!

Thanks In advance!

Re: Holds the First observation and compare with rest of the Dataset

PeterClemmensen — Thu, 25 Jul 2019 14:50:00 GMT

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;

Re: Holds the First observation and compare with rest of the Dataset

PaigeMiller — Thu, 25 Jul 2019 14:51:10 GMT

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).

Re: Holds the First observation and compare with rest of the Dataset

Deva_123 — Thu, 25 Jul 2019 14:56:07 GMT

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

Re: Holds the First observation and compare with rest of the Dataset

PeterClemmensen — Thu, 25 Jul 2019 14:57:11 GMT

Thank you

Re: Holds the First observation and compare with rest of the Dataset

Deva_123 — Thu, 25 Jul 2019 14:57:56 GMT

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