topic Variable names in DI studio in SAS Data Management

Variable names in DI studio

matt_w — Wed, 24 Aug 2011 10:45:54 GMT

I have a question about variable names in DI studio, but specifically about whether it is possible to output a SAS disallowed variable name to an external file XLS or CSV.

I know that SAS does not allow a variable name to start with a number, and that if the result of a transpose is a numeric variable name SAS will prefix with an underscore, so variable 101 becomes _101.

So, I generate a table which looks like

ORG	_101	_102	_103	_104
A	10	15	20	25
B	5	10	15	20
C	20	15	10	5
D	30	25	20	15

I have an existing process which needs data to be presented in CSV format without the underscores

ORG	101	102	103	104
A	10	15	20	25
B	5	10	15	20
C	20	15	10	5
D	30	25	20	15

I produce 100's of output tables so would like to programmatically deal with this problem.

Is there a method in DI studio which will allow me to label my variables as the variable name minus the underscore in the export process? Or is there some other solution to allow me remove the underscore character on export?

Grateful for advise.

Thanks

Matt

Variable names in DI studio

LinusH — Wed, 24 Aug 2011 13:25:44 GMT

Both table and external file objects have an option that permits special characters in object and column/filed names.

I haven't used them extensively, but hopefully it will help you out.

/Linus

Variable names in DI studio

matt_w — Thu, 25 Aug 2011 09:49:05 GMT

Thanks for the swift reply - think I'm almost there but can't see what else I need to do.

I've updated the properties of my external file to accept special characters, and have amended the mapping in my file writer so that _101 maps to 101 etc.

I understand that I need to set the External File Record Start Record to 2 in order for the header row to be written, but when I do this the job fails.

When I set it to 1 the job succeeds but the header row is missing, this appears to show that the setting the accept special characters works but I'm missing a step to allow the file itself to be generated with special characters. I've investigated the VALIDVARNAME = ANY option but cannot get this to work.

Has anyone found a way of addressing this problem?

Thanks

Matt

Variable names in DI studio

matt_w — Fri, 02 Sep 2011 11:33:21 GMT

I ended up raising a track with SAS as I wasn't getting anywhere - think this is down to my lack of previous SAS coding experience, a more experienced user may have found this problem much more quickly.

Turns out there's a bug in the version I'm using (4.21) which incorrectly generates the header rows in the file writer transformation. This is addressed by going into the file writer and amending the section of code which generates the header.

From this:

if ( _n_ = 1 ) then

do;

put

incorrect -> ""Org"n,"_101"n,"_102"n";

end;

To this:

if ( _n_ = 1 ) then

do;

put

correct -> "Org,_101,_102";

end;

And once I've changed this bit of code I'm able to remove the leading underscores and export in my desired format!

SAS tell me this bug is fixed in version 4.3.

Matt