DATA Step, Macro, Functions and more

Creating indicator variable for minimum value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Creating indicator variable for minimum value

Another quick question:  

 

I have multiple observations per ID and multiple variables (in rows) with numbers in character format;  I'm trying to find a way to create an indicator variable "min" that will indicate which observation has the smallest of the numbers (across rows) per ID.

 

My variables are ID and Event1...Event5 (the event variables have numbers).  Does anyone have an idea on what could help?  I tried running the below but it didn't work well.

 

DATA test5;
 SET test3;
 min=.;
	array x{*} ODED_date: ODHD_date: ODOO_date:;
	DO i=1 TO dim(x);
	IF x{i} LT min THEN min=1;
ELSE min=0; END; DROP i; RUN;

  


Accepted Solutions
Solution
‎04-14-2017 12:13 PM
PROC Star
Posts: 7,473

Re: Creating indicator variable for minimum value

Sounds like you are trying to do something like the following:

 

data test3;
  informat ODED_date ODHD_date ODOO_date date9.;
  input id ODED_date ODHD_date ODOO_date;
  cards;
1 1jan2017 1jan2017 1jan2017
1 31dec2016 30nov2016 30nov2016
1 11oct2016 10jan2017 28nov2017
2 31dec2016 30nov2016 30nov2016
2 1jan2017 1jan2017 1jan2017
2 11oct2016 10jan2017 28nov2017
;

proc sql;
  create table test5 as
    select *,
      case 
        when ODED_date eq min(ODED_date) then 1
        else 0
      end as min_ODED_date,
      case 
        when ODHD_date eq min(ODHD_date) then 1
        else 0
      end as min_ODHD_date,
      case 
        when ODOO_date eq min(ODOO_date) then 1
        else 0
      end as min_ODOO_date
        from test3
          group by id
  ;
quit;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Creating indicator variable for minimum value

It helps to provide an example of your input data and what the result for that input may be. A data step is the best way to represent the data as then we can test something that matches you requirement. Data alread in a data set may be turned into data step code Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

You mention that the values are in Event1-event5 but do not show those variables in the code. Please use variable names not descriptions in your problem statement if they are different.

 

You are likely going to nee

Solution
‎04-14-2017 12:13 PM
PROC Star
Posts: 7,473

Re: Creating indicator variable for minimum value

Sounds like you are trying to do something like the following:

 

data test3;
  informat ODED_date ODHD_date ODOO_date date9.;
  input id ODED_date ODHD_date ODOO_date;
  cards;
1 1jan2017 1jan2017 1jan2017
1 31dec2016 30nov2016 30nov2016
1 11oct2016 10jan2017 28nov2017
2 31dec2016 30nov2016 30nov2016
2 1jan2017 1jan2017 1jan2017
2 11oct2016 10jan2017 28nov2017
;

proc sql;
  create table test5 as
    select *,
      case 
        when ODED_date eq min(ODED_date) then 1
        else 0
      end as min_ODED_date,
      case 
        when ODHD_date eq min(ODHD_date) then 1
        else 0
      end as min_ODHD_date,
      case 
        when ODOO_date eq min(ODOO_date) then 1
        else 0
      end as min_ODOO_date
        from test3
          group by id
  ;
quit;

Art, CEO, AnalystFinder.com

 

Super User
Posts: 5,504

Re: Creating indicator variable for minimum value

This is not a difficult problem.  However, you need to spell it out a little better.

 

1. You say you have variables EVENT1 - EVENT5, but then in your program you refer to entirely different names.  What are the right names to use?

 

2. You say they are character variables.  You have to give some examples of what is actually in them.

 

3. You need to appreciate that there can be ties for the minimum value and so more than one row can be flagged with MIN=1.

 

With answers to these questions, though, the programming is relatively easy.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 178 views
  • 0 likes
  • 4 in conversation