BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
Hi all,
There is a situation like this.The vendor giving us the data may or may not give all the fields every month (we get data in excel).i.e in the months jan and feb they gave us a field "Dea_code" so I populate that fileld into SAS. March data did not contain that field. So I'm forcing a " "(blank) for that filed. In april and coming months they may provide us with that field.
my question is, Can we do anything programatically where I can populate the "Dea_code" if present in the excel file or if not present then populate " "(blank).

This is only to avoid manual fixing for each month.
Thanks for your time and help.
SASPhile
4 REPLIES 4
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Yes, you can define a SAS LENGTH or better an ATTRIB statement declaring the variable at some point in your SAS data-input processing flow (likely a DATA step that performs the input handling or one just after a PROC IMPORT). That should be enough - I would not recommend coding a RETAIN statement either.

This approach presumes you have standard definitions maintained for your incoming data-field structures, to mean that the length or type attribute (CHARACTER or NUMERIC) does not change between processing cycles.

Scott Barry
SBBWorks, Inc.
SASPhile
Quartz | Level 8
Hi Scott,
Thanks for the tip. instead of blank " ", if I need to populate "AOB" in the field(Dea_code) if that filed is missing in the excel file and if that field exists in the future data i need to replace "AOB" with those values that are present in excel.

I can hard code "AOB", but if in future I get values for "dea_code" how to replace "AOB" with those values.
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Explore using the MISSING function in a DATA step assignment statement conditionally.

From the SAS-hosted documentation, recommended reading in case you might have to support your SAS program in the future:

MISSING Function
http://support.sas.com/documentation/cdl/en/lrdict/61724/HTML/default/a000509996.htm

DATA STEP CONCEPTS: Reading Raw Data
http://support.sas.com/documentation/cdl/en/lrcon/61722/HTML/default/a001112330.htm



Scott Barry
SBBWorks, Inc.
ballardw
Super User
Having been involved in this situation I would first say it is not a SAS programming problem but a contract problem. Your contract with the vendor should have specified the data layout and they should use that specification.

If you don't start early in a project you'll be fighting re-ordered columns, differently named columns and likely different formats for years.

(I was the 'vendor' in this case with the client sending us data files that changed as often as twice weekly, but the principal was the same.)

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