DATA Step, Macro, Functions and more

Excel text function and SAS put function

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

Excel text function and SAS put function

Dear all,

I have a SAS data set(mydata.sas7bdat). It looks like the following:

Field1

24

3872

349

1111

If I use text function in Excel, I can convert the numbers to character values using =text(A2, "0000").

Field2

0024

3872

0349

1111

How can I do that in SAS?



Accepted Solutions
Solution
‎05-02-2012 05:55 PM
Respected Advisor
Posts: 3,124

Re: Excel text function and SAS put function

Not sure if this is what you want:

data have;

input Field1;

cards;

24

3872

349

1111

;

data want (keep=_c rename=_c=Field1);

set have;

_c=put(field1,z4.);

run;

proc print;run;

Regards,

Haikuo

View solution in original post


All Replies
Valued Guide
Posts: 2,174

Re: Excel text function and SAS put function

ODS tagsets.excelxp

provides a way to do this

It allows you to define the custom format for excel to apply the format

It also allows you to apply an excel formula

There are many examples in the ODS forum

Solution
‎05-02-2012 05:55 PM
Respected Advisor
Posts: 3,124

Re: Excel text function and SAS put function

Not sure if this is what you want:

data have;

input Field1;

cards;

24

3872

349

1111

;

data want (keep=_c rename=_c=Field1);

set have;

_c=put(field1,z4.);

run;

proc print;run;

Regards,

Haikuo

Contributor
Posts: 40

Re: Excel text function and SAS put function

Thank you, Haikuo. It works!

Super Contributor
Posts: 1,636

Re: Excel text function and SAS put function

or

data have;

input Field1;

cards;

24

3872

349

1111

;

proc sql;

  select put(field1,z4.) as field1

    from have;

quit;

Super Contributor
Posts: 276

Re: Excel text function and SAS put function

data have;

input Field1;

cards;

24

3872

349

1111

;

run;

data have1;

set have;

if length(field1)=1 then "000"||field1;

else;

if length(field1)=2 then "00"||field1;

else;

if length(field1)=3 then "0"||field1;

else field1=field1;

run;

Regards

Sanjeev K

Valued Guide
Posts: 2,174

Re: Excel text function and SAS put function

SAnjeev

YOu have to learn about the Z. format. The example a few postings earlier in this thread show how much it improves on your solution.

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 887 views
  • 0 likes
  • 5 in conversation