BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
cyoung
Fluorite | Level 6

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;

  

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

3 REPLIES 3
ballardw
Super User

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

art297
Opal | Level 21

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

 

Astounding
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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