Help using Base SAS procedures

[newbie] Ordering output in the same order as input

Reply
Occasional Contributor
Posts: 5

[newbie] Ordering output in the same order as input

I have an informat file and an output format file.  The informat file contains the names/data types of all of the variables being read in from an input text file.  The format file contains just the variable names/formats that require special formatting or need to be renamed, etc..... So what is happening is that the actual dataset that gets outputted is first following the order of the variables listed in the format file, and then picks up with the remaining variables in the informat file.  However, I want the output dataset to maintain the exact order of the informat file.

Is there a way to use the format file strictly for formatting, but to ignore the order that is present in that file, and just keep the original ordering?

Regular Contributor
Posts: 244

Re: [newbie] Ordering output in the same order as input

You're not terribly specific about how you're doing this, but I assume you're doing something like

data mydata;

%include formatfile;

%include informatfile;

input...;

run;

If so, then just put the informatfile first (before the formatfile) and it will define the order.  SAS defines variable order solely in the order it runs into things in the data step; so if the formatfile is first, it will see all of the variables in that file, define them, then move on to the informatfile.

Occasional Contributor
Posts: 5

Re: [newbie] Ordering output in the same order as input

First of all, thank you very much for taking time to reply.  You're assumption was correct in how I am trying to do this.  I took your advice and put the informat before the format and it kept the ordering as you suggested. However, now it appears to be ignoring the format commands in the format file...Any suggestions?

This is what it currently looks like:

data test (compress=binary);   

infile tst_inp linesize=3000;   

input       

      %include "!SASPROG/tst_fields.inc";    

;   

%include "!SASPROG/tst_format.inc";   

run;

Regular Contributor
Posts: 244

Re: [newbie] Ordering output in the same order as input

Does tst_fields.inc have a RUN statement in it?  It probably does, if the formats are being ignored.  That's a problem without a really good solution without editing these files.  The best solution is probably to include the tst_format.inc in the tst_fields.inc file before its run statement but after the input statement.  The even better solution is to get the format file to contain _all_ variables you need, if that's possible.  RETAIN like Mit suggested is another possibility, as long as you always input all variables on each line (as long as there's no conditional input or anything like that in tst_fields.inc).

Occasional Contributor
Posts: 5

Re: [newbie] Ordering output in the same order as input

I was mistaken...the FORMAT commands are not being ignored as I previously thought.  I also have LABEL commands in the same tst_format.inc file that I mentioned, and they are being ignored, so I assumed the entire file was being ignored.  However, I verified that it is only the Label command that is not being taken into account. For example:

LABEL lst_name      = "LAST NAME";

FORMAT lst_name        $50.;

The output from this has 50 characters, however it shows up in the dataset as "lst_name".

It would be nice if the LABEL worked as well, but right now having the format and the correct overall ordering of every variable is more important.

Super User
Super User
Posts: 6,495

Re: [newbie] Ordering output in the same order as input

If there are multiple LABEL statements for the same variable then the last one "wins".

Occasional Contributor
Posts: 5

Re: [newbie] Ordering output in the same order as input

That makes sense, but there's definitely only one LABEL statement here. It only "picks up" the LABEL statement if I read in the tst_format.inc file prior to reading in the tst_informat.inc file.  However, if I do that then that puts me back in my original predicament...So by reading in the tst_informat.inc file first, I get to maintain the correct variable order (which is what I wanted), the FORMAT statements in the tst_format.inc file still work (which is what I wanted), BUT the variable names, as they are listed in the tst_informat.inc file, seem to be unaffected by the LABEL commands in the tst_format.inc file (which is Not what I wanted).

Regular Contributor
Posts: 244

Re: [newbie] Ordering output in the same order as input

LABEL works like FORMAT, the order doesn't matter except if there are two labels for the same variable.  You need to post the contents of your formats/label files as there's something you're not explaining quite right.

Super User
Super User
Posts: 6,495

Re: [newbie] Ordering output in the same order as input

Are we just talking about the permanently attached formats? Or by formatting do you mean something else.

In a single step SAS will use the final format that is assigned to a variable when there are multiple FORMAT statements.

For example the HT variable will use 5.2 as the format if you have code like this:

data want ;

  set have ;

  format ht 4.1 ;

  format ht 5.2 ;

run;

So if your FORMAT file just have some format (and/or INformat statements) perhaps it is as simple as including it twice?

data want;

  %inc formats;

   %inc inputs;

  %inc formats;

run;

But in reality you might be better off having definition file so that your code looks more like.

data want ;

   attrib var1 length=8 format=date9. informat=date9.;

   attrib var2 length=$30 ;

   ...

   infile ... ;

   input var2

          .... var1 ...

    ...

run;

Frequent Contributor
Frequent Contributor
Posts: 83

Re: [newbie] Ordering output in the same order as input

%ler var=var1 var2 var3;  *list the variables in the order you would like to.

Data want;

retain &varlist.;

set data have;

keep &varlist.;

run;

Frequent Contributor
Frequent Contributor
Posts: 76

Re: [newbie] Ordering output in the same order as input

By any chance are you looking at the "data" that shows in EG rather than output from a procedure.   If my memory serves me, the column names are the variable names.  However if you mouse over each of the column headings, or check the properties of the variables (proc contents) you will see that the labels are indeed there. 

So in the simple example;

Data chk;

  input lst_name;

LABEL lst_name      = "LAST NAME";

FORMAT lst_name        $50.;

run;

The data chk will still show lst_name as the variable name.  However, if you do a proc report, the label should show up.

Occasional Contributor
Posts: 5

Re: [newbie] Ordering output in the same order as input


Thanks Lap. You were absolutely right.  I was using the PROC LIST command and was not seeing the label name in the output. I changed it to PROC REPORT, and now I see it.

Ask a Question
Discussion stats
  • 11 replies
  • 318 views
  • 0 likes
  • 5 in conversation