topic Re: How to drop a variable when criteria is met in SAS Programming

How to drop a variable when criteria is met

BETO — Fri, 13 Mar 2020 18:07:57 GMT

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

Re: How to drop a variable when criteria is met

Kurt_Bremser — Fri, 13 Mar 2020 18:11:25 GMT

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.

Re: How to drop a variable when criteria is met

BETO — Fri, 13 Mar 2020 18:28:22 GMT

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

Re: How to drop a variable when criteria is met

ballardw — Fri, 13 Mar 2020 18:49:37 GMT

@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.

Re: How to drop a variable when criteria is met

PhilC — Fri, 13 Mar 2020 20:01:58 GMT

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.

Re: How to drop a variable when criteria is met

PhilC — Fri, 13 Mar 2020 20:19:18 GMT

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.