Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

Im looking for a better way to do this. I have a data set t...


Russian Wyatt 2

Recommended Posts

Im looking for a better way to do this.

I have a data set that looks like this (shortened for brevity):

 

 

 

 

LinkID

Wage_Q1

Wage_Q2

Wage_Q3

Wage_Q4

Wage _Q20

 

 

 

 

Jsmith

12000

14000

14000

16000

.

 

 

Jdoe

0

18000

18000

18000

18000

 

 

bjean

15500

16000

0

.

.

 

 

Tjones

.

.

4200

6000

,

 

 

 

Im trying to create a define/compute field that for each ID, checks to see if I have at least four quarters worth of wages and returns Yes or No. There are some other fields between LinkID and Wage_Q1 and some others at the end.

Some values in the data are missing

The wage values do not have to be in consecutive quarters just above 0.

Any suggestions

Link to comment
Share on other sites

Seems like you could use something like what Im doing on the ggtest data below. The result of a logical test is either 1 or 0 which can actually be added together.

-* Make up some data to test with.

TABLE FILE ibisamp/ggsales

SUM COMPUTE Wage_Q1/I11 = DOLLARS;

COMPUTE Wage_Q2/I11 = IF REGION EQ 'Midwest' THEN 0 ELSE BUDDOLLARS;

COMPUTE Wage_Q3/I11 = UNITS;

COMPUTE Wage_Q4/I11 MISSING ON = IF REGION EQ 'Southeast' THEN MISSING ELSE BUDUNITS;

BY REGION AS 'LinkID'

ON TABLE SET HOLDATTR OFF

ON TABLE SET ASNAMES MIXED

ON TABLE SET HOLDLIST PRINTONLY

ON TABLE HOLD AS ggtest FORMAT FOCUS

END

 

-* Use the technique of adding up logical 1s and 0s.

TABLE FILE ggtest

PRINT Wage_Q1 Wage_Q2 Wage_Q3 Wage_Q4

COMPUTE Count/I9 = (Wage_Q1 GT 0) + (Wage_Q2 GT 0) + (Wage_Q3 GT 0) + (Wage_Q4 GT 0) ;

BY LinkID

END

Link to comment
Share on other sites

Another idea, in Define, you can do:

 

N_WAGE_Q1/I1 = IF Wage_Q1 GT 0 THEN 1 ELSE 0;

N_WAGE_Q2/I1 = IF Wage_Q2 GT 0 THEN 1 ELSE 0;

N_WAGE_Q3/I1 = IF Wage_Q3 GT 0 THEN 1 ELSE 0;

N_WAGE_Q4/I1 = IF Wage_Q4 GT 0 THEN 1 ELSE 0;

 

Y_N/A3 = IF (N_WAGE_Q1 + N_WAGE_Q2 + N_WAGE_Q3 + N_WAGE_Q4) GT 4 THEN YES ELSE NO;

Link to comment
Share on other sites

I had to use a DEFINE, as the COMPUTE would not write to XFOCUS file or show up in my browser when I used: ON TABLE SET BYDISPLAY ON.

Just FYI: I tried to save myself some time by adding it to a report using info-assist it does not like Davids method. I originally had something similar to what Pku suggested (and works with info-assist), but I figured there was a cleaner way to do it.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...