topic Re: Replacing values of a column with its minimum value in New SAS User

Replacing values of a column with its minimum value

npr — Tue, 03 Dec 2019 14:37:23 GMT

i am very new to sas and I have the following work table

I want to create a new table in which column Date and Z remain the same, but all values in column X are replaced with the minimum value in column X and all values in column Y are replaced with the minimum value in column y.

Sample output is as follows

Re: Replacing values of a column with its minimum value

novinosrin — Tue, 03 Dec 2019 14:44:04 GMT

Hi @npr Where is the sample input and expected output?

Re: Replacing values of a column with its minimum value

KachiM — Tue, 03 Dec 2019 15:41:18 GMT

@npr

You can get the minimum of X and Y externally using ProcSQL. Then you can replace the original X and Y by min X and min Y in a Data Step. Assumed your input data is named as HAVE.


proc sql noprint;
   select min(X), min(Y) into :minX, :minY
   from have
   ;
quit;

%put &minX;
%put &minY;

data want;
   set have;
   X = &minX;
   Y = &minY;
run;

Another way is to use a single Data step. In the first part you can get the minimum of X and minimum of Y and in the second part the original X and Y are replaced by the corresponding minimum values of X and Y.


data want;
   if _n_ = 1 then do until(eof);
      set have end = eof;
      if not missing(X) then minX = min(minX, X);
      minY = min(minY, Y);
   end;
   retain minX minY;
   set have;
   X = minX;
   Y = minY;
drop minX minY;
run;

Re: Replacing values of a column with its minimum value

Tom — Tue, 03 Dec 2019 15:48:06 GMT

Don't replace the data (as you will lose information). Instead create a new dataset with the values you want.

proc sql ;
  create table want as
    select date,x, min(y) as y, min(z) as z
    from have 
  ;
quit;

Re: Replacing values of a column with its minimum value

npr — Wed, 04 Dec 2019 08:10:24 GMT

Thanks Tom . Just another add on question , if i only want values from 2002Q1 to be replaced with the minimum value , how do i go about it ?

Re: Replacing values of a column with its minimum value

npr — Wed, 04 Dec 2019 08:23:52 GMT

proc sql;
create table work.macro_eco_new as
select date, x, y, z
If date >= "2002Q1",then select min(x) as x, min(y) as y
from work.macro_eco
;
quit;

This is what i tried