topic How to retrieve the complete format of variable? in SAS Programming

How to retrieve the complete format of variable?

JohnChen_TW — Fri, 05 May 2017 10:27:13 GMT

As far as I know, all items and attributes for features in dataset would be automatically saved in SASHELP.

And I need to use PUT function to create a new variable in the macro. So, I tried to retrieve the variable format (e.g. $SEXOF6.) from SASHELP but it just recorded the char part as "$SEXOF.". Why?

Does anyone have a suggestion to retrieve the complete format?

Re: How to retrieve the complete format of variable?

Kurt_Bremser — Fri, 05 May 2017 10:43:14 GMT

It depends on how you applied the format to the variable.

proc format library=work;
value $mysex
  'M' = 'Male'
  'F' = 'Female'
;
run;

data class;
set sashelp.class;
format sex $mysex6.;
run;

proc sql;
/*create table test as*/
select name, format from dictionary.columns
where libname = 'WORK' and memname = 'CLASS';
quit;

data class1;
set sashelp.class;
format sex $mysex.;
run;

proc sql;
/*create table test as*/
select name, format from dictionary.columns
where libname = 'WORK' and memname = 'CLASS1';
quit;

Look at the result:

Column Name                       Column Format
-----------------------------------------------------------------------------------
Name                                                                               
Sex                               $MYSEX6.                                         
Age                                                                                
Height                                                                             
Weight                                                                             

Column Name                       Column Format
-----------------------------------------------------------------------------------
Name                                                                               
Sex                               $MYSEX.                                          
Age                                                                                
Height                                                                             
Weight

Re: How to retrieve the complete format of variable?

RW9 — Fri, 05 May 2017 10:43:37 GMT

We have no way of telling from what you have posted. Post test data in the form of a datastep! If you have some code post that, and post what you want out at the end. Sashelp.vcolumn -> Format should contain the format name and length, so how did you define the format as thats not a SAS format.

Re: How to retrieve the complete format of variable?

Reeza — Fri, 05 May 2017 12:00:44 GMT

What do you mean by retrieve the complete format? Are you looking for the format definition (ie proc format mapping) or something else?

Re: How to retrieve the complete format of variable?

JohnChen_TW — Tue, 09 May 2017 08:14:44 GMT

My question is, I cannot know the format of each variable from the external files in advance.
After googled, I just know that the format will be saved in sashelp.columns. But it will not save the format with the number when the actual format is $SEXOF6..

1. What does the number of formats mean?
2. How to get the complete format from the external files in advance?

Re: How to retrieve the complete format of variable?

Kurt_Bremser — Tue, 09 May 2017 08:30:44 GMT

The metadata for external files are NOT to be found in sashelp.vcolumn or dictionary.columns. Those only deal with SAS datasets in currently assigned libraries. sashelp.column (note the missing v) is a static table supplied at SAS installation time that has nothing to do with current metadata.

What do you mean by "number of formats"? I can't find such a variable anywhere.

For your question 2, the formats for external files have to be determined by you from the documentation that accompanies such files. External files without documentation are worthless and should be returned to sender.

For how formats are stored in dictionary.columns (sashelp.vcolumn is just a view to that), see my earlier post.

Re: How to retrieve the complete format of variable?

RW9 — Tue, 09 May 2017 08:36:57 GMT

There is several parts to your question here. Firstly, for external files you cannot use SAS metadata libraries, that is only for files which appear in assigned libnames. For external files you should have a data transfer specification which details all the structure of the file. If you do not have this then you will be guessing and it is a bad import process.

Secondly formats are:

Where type is $ for character and missing for numeric.

Name is the name of the format.

Length is the maximum length in characters of the format.

So $SEXOF6

Means it is character, called SEXOF, and has a maximum length of 6 characters.

Now Where do you get the information that there is a format $SEXOF6? If you can provide complete information -what type of files (examples?) documentation etc. as we are just huessing what your trying to do here.