Field Width

[johnhinton]

I've got a query that is pulling back 30 million or so rows.  It's working, but the issue is there is a macro that is searching through the first 10,000 rows to determine the width of each field.  Unfortunately I have some fields that are all blank for the first 10,000 records, but have values afterwards.  These are set to size 1$, which is causing issues.  I tried sorting on the small field, but then another has the same issue.  My thought is to change the blank value to 9999999, so it's wide enough to work, then later take these back to 0.  I'm getting an error doing this.  Here's the code I'm trying:

IF WRITPREM = . THEN DO;

WRITPREM = 9999999;

END;

ELSE WRITPREM

AS WRITPREM;

END;

Thanks for any help or ideas.

[hashman]

@johnhinton:

1. Your field in question is apparently character, and yet you're testing for a standard numeric missing value and assigning the numeric value 9999999 to it. 
2. Your syntax is wrong: The DATA step language doesn't know what AS in this context means.

Simply code:

IF cmiss (WRITPREM) THEN WRITPREM = "9999999" ;

And on the way back, you'd have to change WRITPREM back to blanks, not to 0, i.e. WRITPREM="".  

Apropos, whoever has written the macro you've mentioned should have accounted for this kind of eventuality in the macro code instead of making assumptions about data. 

Kind regards

Paul D.

[Reeza]

Where are you pulling the data from that requires you to specify the field length or types? 

Anyone making a 30 million row text file would have access to the record layout.

---

@johnhinton wrote:

I've got a query that is pulling back 30 million or so rows.  It's working, but the issue is there is a macro that is searching through the first 10,000 rows to determine the width of each field.  Unfortunately I have some fields that are all blank for the first 10,000 records, but have values afterwards.  These are set to size 1$, which is causing issues.  I tried sorting on the small field, but then another has the same issue.  My thought is to change the blank value to 9999999, so it's wide enough to work, then later take these back to 0.  I'm getting an error doing this.  Here's the code I'm trying:

IF WRITPREM = . THEN DO;

WRITPREM = 9999999;

END;

ELSE WRITPREM

AS WRITPREM;

END;

Thanks for any help or ideas.

---

[ballardw]

How many of these data sets do you have??? And are your repeatedly running the same macro against the same data set? If so why?

If the purpose is to determine the lengths of values stored one might ask why the length of the field is such that you don't already have a close idea of the size.

And what do you do with that length information once you have it?

I would expect, as @Reeza commented, that there should be some documentation that perhaps is not being properly utilized.