BookmarkSubscribeRSS Feed
BETO
Fluorite | Level 6
Hi I have a table 52 columns I need to remove some of the variable that matches criteria .here is example of table
Id. date. Col Col1. Col2 Cola3 Duration
A1. 03/01/20. A. B. C. D. 1
A2. 03/01/20. B. C. D. A. 4

What I want to see
Id. date. Col Col1. Col2 Cola3 Duration
A1. 03/01/20. A. B. C. D. 1
A2. 03/01/20. C. D. A. 4
I used alter
But drops the whole col just need it to drop a section of the row
5 REPLIES 5
Kurt_Bremser
Super User

You cannot conditionally drop a variable from one row, you can only set it to missing.

Or, in an array of variables of equal type, move all values to the left, so that the last one is missing.

BETO
Fluorite | Level 6
Would you be able to show me how to move everything in one row where duration is greater then 3 min ?
ballardw
Super User

@BETO wrote:
Would you be able to show me how to move everything in one row where duration is greater then 3 min ?

Can you show example data of what the starting values are and what the desired result would be? Best is to provide the data in the form of a data step so we don't have to guess about variable types or names. And what you ask may not make sense if the variables are mixed numeric and character.

PhilC
Rhodochrosite | Level 12

Drop is the wrong concept, I think.  Maybe transposed data would help.

 

Assuming ID and date are necessary to separate cases.  You can start with what you have, then using PROC TRANSPOSE to create data like this: 

 

A1. 03/01/20. A. 
A1. 03/01/20. B. 
A1. 03/01/20. C. 
A1. 03/01/20. D. 
A2. 03/01/20. B. 
A2. 03/01/20. C. 
A2. 03/01/20. D. 
A2. 03/01/20. A.

writing your elimination logic in a DATA step this data would become:

 

A1. 03/01/20. A. 
A1. 03/01/20. B. 
A1. 03/01/20. C. 
A1. 03/01/20. D. 
A2. 03/01/20. C. 
A2. 03/01/20. D. 
A2. 03/01/20. A.

the logic removes this record:

A2. 03/01/20. B. 

When re-transposing Id use a "Do-Whitlock" loop and then recombining with the original data to re-merge duration values to arrive at what you want.

 

You can do something similar with arrays.  that's not as easily explained.

PhilC
Rhodochrosite | Level 12

Third idea:

 

 

Id. date.     Journal Duration
A1. 03/01/20. ABCD. 1 A2. 03/01/20. BCDA. 4

Take what you have but save and concatenate Col: variables into long strings.  We over here, call strings names like this "Journey" or "Journal" because they usually are chronologically ordered for us.  Using appropriate logic, change the Journal strings with Find(), Scan(), SubStr(), TranWRD(), etc. to arrive at what you want.

 

I employ regular expressions (i.e. PRXmatch() etc.)  to make matches in this context, because it eliminates a lot of if-then structures, but maybe your logic is simple enough to avoid regexp.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 5 replies
  • 523 views
  • 0 likes
  • 4 in conversation