SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Formatting data to external extract file

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Formatting data to external extract file

I am a novice writing my first program and am stuck in regards to fomatting rules when creating new output data. This should be pretty simple I am thinking

I have an input external file which contains among other things a date and a client, app, and an account number defined as;

000081 INPUT                                  

000082  @    1 CLIENT      3.                 

000083  @    5 LNTYP       3.                 

000084  @    9 PYFCOFCD   $2.                 

000085  @   12 OGLDSBDT    8.                 

000086  @   12 OGLDSBCC    4.                 

000087  @   16 OGLDSBMM    2.                 

000088  @   21 APP         2.                 

000089  @   24 LN         13.                 

000090  @   38 PRICUSNM  $30.                

OGLDSBDT contains 20140214     (YYCCMMDD format)

CLIENT contains 111, APP contains 22 and LN contains 3333333333333

I want to create a new file where the date will be formatted as 2014/02 where the '/' s included and the DD portion of the date is dropped.

For the CLIENT, APP and LN I want to create a single field that will be formatted like so 111-22-3333333333333.

These fields will be 'PUT' to an external extract file.

The book I am using has no examples that show how I can perform formats such as these.


Accepted Solutions
Solution
‎02-14-2014 06:51 PM
Super User
Super User
Posts: 7,076

Re: Formatting data to external extract file

Do you want to create a new text file or would you rather have a SAS dataset?

Are you id variables like CLIENT really numbers?  Even if they are all digits you might be better served to treat them as character strings.  For example what if I am APP number 1.  Should that be '1', ' 1', or '01' ?

You can use YYMMS7. format to print a date as YYYY/MM .  So you should read in the dates as an actual date using YYMMDD8. informat instead of treating it as a 8 digit number.

input .... OGLDSBDT yymmdd8. .... ;

If you are building an actual dataset and what to create a composite ID variable then use CATX() to string them together.  But note that it will trim the values.  So again if APP is less then 10 or CLIENT is less than 100 what format do want for this composite id?

If you just writing to a text then PUT can take care of that.

put ... client Z3. '-' app Z2. '-' ln z13. ....

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Formatting data to external extract file

Read the data originally for the OGLDSBDT variable with an appropriated date format, in this case

OGLDSBDT YYMMDD8.

Then use an appropriate format for display as needed. You can associate a default format by adding

Format OGLDSBDT YYMMs7.;

There are a large number of display formats for dates as well as functions to manipulate dates and I would recommend not actually dropping the day component.

If you know what the maximim length of the combination would be preface it with a statment like:

Length LongString $ 20;

To create a variable to handle combining the three variables would be

LongString = catx('-',Client,App,Ln);


Depending on what you are trying to accomplish you may want to use ODS or Proc Export to create your output file as you may get results quicker than using a Data _Null_ step with Put statements.

Solution
‎02-14-2014 06:51 PM
Super User
Super User
Posts: 7,076

Re: Formatting data to external extract file

Do you want to create a new text file or would you rather have a SAS dataset?

Are you id variables like CLIENT really numbers?  Even if they are all digits you might be better served to treat them as character strings.  For example what if I am APP number 1.  Should that be '1', ' 1', or '01' ?

You can use YYMMS7. format to print a date as YYYY/MM .  So you should read in the dates as an actual date using YYMMDD8. informat instead of treating it as a 8 digit number.

input .... OGLDSBDT yymmdd8. .... ;

If you are building an actual dataset and what to create a composite ID variable then use CATX() to string them together.  But note that it will trim the values.  So again if APP is less then 10 or CLIENT is less than 100 what format do want for this composite id?

If you just writing to a text then PUT can take care of that.

put ... client Z3. '-' app Z2. '-' ln z13. ....

Occasional Contributor
Posts: 8

Re: Formatting data to external extract file

Thanks Tom. In the end I selected my extract from the main external file into a SAS file . I then input the SAS file and created a temporary external file and unpacked the data fields to display numeric fields. I then input the external final and was able to define the date as individual alphanumeric fields which then allowed me to get at the internal MM month data and to format it is as per your examples.

Thanks

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 332 views
  • 0 likes
  • 3 in conversation