topic Re: Inserting a column into a data set in SAS Programming

Inserting a column into a data set

jacksonan123 — Fri, 14 Feb 2020 00:25:40 GMT

If I have a data set A:

ID Ratio

1 5

. 5

10 5

Data set B:

ID Time conc

1 0 0

. 1 0.5

24 24 0.01

The (.) means that there are several values for A that end at 10 while for B they end at 24. The question is how can I introduce the constant ratio value 5 into data B from data A for all the rows in data set B?

Re: Inserting a column into a data set

Tom — Fri, 14 Feb 2020 01:14:46 GMT

I don't follow. You want a constant?

data want;
  set B;
  ratio=5;
run;

You want the constant that is in dataset A? Just take the first copy of it.

data want;
  set B;
  if _n_=1 then set A(keep=ratio);
run;

If you want something else you need to explain the rules in more detail.

Re: Inserting a column into a data set

jacksonan123 — Fri, 14 Feb 2020 03:55:00 GMT

I tried your solution but it put in the column for ratio, but it was all
missing values. Let me try to explain again with my data set want.

Data a;

ID Ratio

1 2.5

. 2.5

10 2.5

Data B;

ID Time Conc

1 0 0

. 1 0.5

24 24 0.25

Data Want:

ID Time Conc Ratio

1 0 0 2.5

. 1 0.5 2.5

24 24 0.25 2.5

Re: Inserting a column into a data set

Tom — Fri, 14 Feb 2020 04:35:52 GMT

Post actual data. Like this:

data a;
  do id=1 to 10;
    ratio=2.5;
    output;
  end;
run;

data b;
  id=0;
  do time=1 to 24 ;
    id=id+1;
    conc = round(ranuni(0),0.01);
    output;
  end;
run;

Now use that data:

data want;
  set b;
  if _n_=1 then set a(keep=ratio);
run;

Result:

Obs    id    time    conc    ratio

  1     1      1     0.31     2.5
  2     2      2     0.74     2.5
  3     3      3     0.52     2.5
  4     4      4     0.84     2.5
  5     5      5     0.09     2.5
  6     6      6     0.33     2.5
  7     7      7     0.11     2.5
  8     8      8     0.87     2.5
  9     9      9     0.61     2.5
 10    10     10     0.43     2.5
 11    11     11     0.92     2.5
 12    12     12     0.79     2.5
 13    13     13     0.91     2.5
 14    14     14     0.98     2.5
 15    15     15     0.41     2.5
 16    16     16     0.50     2.5
 17    17     17     0.21     2.5
 18    18     18     0.55     2.5
 19    19     19     0.31     2.5
 20    20     20     0.38     2.5
 21    21     21     0.43     2.5
 22    22     22     0.49     2.5
 23    23     23     0.71     2.5
 24    24     24     0.96     2.5

The only way that running that step would result in any missing values for RATIO would be if the variable already existed in dataset B. In that case you could just drop it.

data want;
  set b(drop=ratio);
  if _n_=1 then set a(keep=ratio);
run;

It is still not clear why you made the A dataset have 10 observations instead of one.

Re: Inserting a column into a data set

jacksonan123 — Fri, 14 Feb 2020 10:08:00 GMT

When data A was generated it had some other values that were needed other
than ratio.

Your answer was a solution and in the future when possible I will submit
data as you suggested.

I had always thought that when one set a data set meant that one data set
was placed on top of the other and merge was the way to combine but I stand
corrected by you code.

Re: Inserting a column into a data set

Tom — Fri, 14 Feb 2020 14:46:20 GMT

SET (and MERGE) are executable statements. Each time it execute it reads another observation from the dataset(s).

That is why you can do code like this to process all of the records for a group in iteration of the data step.

do until(last.id);
  set have;
  by id;
 ....
end;

Re: Inserting a column into a data set

jacksonan123 — Fri, 14 Feb 2020 15:54:00 GMT

Thanks for the explanation.