topic Re: Repeat a value in different rows with the same key in New SAS User

Repeat a value in different rows with the same key

Sweetplanet — Fri, 07 Dec 2018 13:05:01 GMT

I have this situations and i would like to repeat the values in all of the rows with the same key. Anyone can help me?

For example in the column D200306 i need the value 661.35 in the seven rows for that key.

Re: Repeat a value in different rows with the same key

PeterClemmensen — Fri, 07 Dec 2018 13:18:41 GMT

Just to be clear, you mean in the seven rows for that key, right?

Re: Repeat a value in different rows with the same key

Sweetplanet — Fri, 07 Dec 2018 13:21:37 GMT

Yes, for that key.

I have a lot of groups of rows with differents keys.

Re: Repeat a value in different rows with the same key

PeterClemmensen — Fri, 07 Dec 2018 13:27:10 GMT

So you want 661.35 to replace every missing value I can see here?

Or do you want 661.35 to replace all the missing values in the first row, 604.52 to replace all the missing values in the second row and so on?

Re: Repeat a value in different rows with the same key

Ksharp — Fri, 07 Dec 2018 13:30:06 GMT

data have;
input key $ d1 d2 d3;
cards;
1 1 . .
1 . 2 .
1 . . 3
;
run;
proc summary data=have nway;
class key;
var _numeric_;
output out=temp sum=;
run;
data want;
 set temp;
 do i=1 to _freq_;
  output;
 end;
 drop i _:;
run;

Re: Repeat a value in different rows with the same key

RW9 — Fri, 07 Dec 2018 13:31:05 GMT

You can use proc summary to get one row for that data with all the values populated. Something like:

proc summary data=have;
  class key;
  var d:;
  output out=want sum= / autoname;
run;

Or do you mean you still want 7 rows in the output dataset? If so then that is trickier. Probably need to normalise the data to get something like:
key col val

Then sort by key removing missings, then merging that back onto the data, setting where missing to be the merged value. Then transpose up.

Post test data in the form of a datastep!!

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Not going to type in test data just to have something to work on.

Re: Repeat a value in different rows with the same key

Sweetplanet — Fri, 07 Dec 2018 13:33:37 GMT

661.35 in every missing value by column. I mean, in the column D200306 the value 661.35 in each row, the value 604.51 in the column D200307 in each row and so on.

Re: Repeat a value in different rows with the same key

PeterClemmensen — Fri, 07 Dec 2018 13:35:40 GMT

If the latter is the case then do

data have;
input key $8. D200306 D200307 D200308;
datalines;
BRR9934X 1 . .
BRR9934X . 1 .
BRR9934X . . 1
;

data want(drop=i);
   set have nobs=nobs ;
   array vars{*} D2003:;
   do i=1 to dim(vars);
      if vars[i]=. then vars[i]=coalesce(of vars[*]);
   end;
run;

Re: Repeat a value in different rows with the same key

Sweetplanet — Fri, 07 Dec 2018 13:35:41 GMT

Really in the example i show you, i would like to have only one row for key and the values thats appears in the columns only in one row.

Re: Repeat a value in different rows with the same key

Kurt_Bremser — Fri, 07 Dec 2018 13:57:47 GMT

Fix your data structure on the way:

proc transpose
  data=have
  out=trans (
    rename=(_name_=period)
  )
;
by key;
var d:;
run;

data want;
set trans;
value = coalesce(of col:);
drop col:;
run;

That way you get a nice, long dataset that's easy to work with. See Maxim 19.

Re: Repeat a value in different rows with the same key

LinusH — Fri, 07 Dec 2018 14:41:06 GMT

May I challenge ther requirement.

If I unserstand you correctly, you wish to store seven rows with identical values in all columns?

What is the use of that?

IMO condensng to one row, or transposing so you'll have something like ID, DC_num, and DC_val (seven rows) will give you uniqueness and a normaized data structure.

Re: Repeat a value in different rows with the same key

Astounding — Fri, 07 Dec 2018 15:40:35 GMT

If you want just one row per KEY, this becomes a much simpler problem. Assuming your data is sorted by KEY:

data want;

update have (obs=0) have;

by key;

run;