CODEWITHSUNDEEP
sqlsas
benmeira
benmeira

Reputation: 3

How can I replace a numeric column with a character column of fixed width?

I have a table that contains a zip code field (numeric type), and some of the zip codes contain only 4 digits. I need to pad the 4 digits zip codes with leading spaces. I created a character filed as follows:

proc sql;
create table myTable as
select * , put(Zip,5.) as ZipChar
from Mytable;

create table myTable as
select *, case when Zip<10000 then " "||ZipChar else ZipChar end as Zip_Fixed
from Mytable;
quit;

Now my difficulty is how to locate Zip_Fixed instead of Zip column. Zip is a numeric type and Zip_Fixed is a character type. the replacement is necessary as the order of the column must be kept. I'm all ears for any other creative solution.

Thanks, Adi

Upvotes: 0

Views: 276

Answers (3)

Tom
Tom

Reputation: 51621

Your use of the PUT() function will create a character field with leading spaces. Your second step will add another leading space.

Why not just use leading zeros instead? Then the values will look more like numbers and still sort properly.

put(zip,Z5.)

If the final goal is to create a text file with fixed width fields (as one of your other comments mentions) then you just use the format in the PUT statement you use to write the text file.

data _null_;
  set mytable ;
  file 'myfile.txt'; 
  put ... zip 5. ... ;
run;

Upvotes: 2

Sean
Sean

Reputation: 1120

I wrote a macro that reorders variables many months ago. It's probably not the shortest way of doing this, but it should solve your problem.

Assume you have a dataset and want to move move_me before v1

data temp;
   input v1 v2 v3 v4 v5 move_me;
   datalines;
    1 2 3 4 5 0
    1 2 3 4 5 0
    1 2 3 4 5 0
    ;
run;

Run the %order macro below:

%macro order(dsn, var1, before_or_after, var2);

/*  get list of variables in your dataset from dictionary.columns*/
    proc sql;
        create table vars as select 
            varnum, name
            from dictionary.columns
            where memname = upcase("&dsn.");
    quit;

/*  assign the final position of the variable that you want to move*/
    proc sql;
        create table vars2 as select
            a.*,
            case when a.name = "&var1." then max(b.varnum) else . end as varnum_want
            from vars as a
            left join vars (where = (name = "&var2.")) as b
            on a.varnum = b.varnum;
    quit;

/*  move the variable to that location*/
    data vars3 (drop = varnum_want);
        set vars2;

        %if &before_or_after. = before %then %do;
            if name = "&var1." then varnum = varnum_want - 0.5;
        %end;

            %else %if &before_or_after. = after %then %do;
                if name = "&var1." then varnum = varnum_want + 0.5;
            %end;

            %else %do;
                putlog "ERROR: Pick 'before' or 'after'";
            %end;

        proc sort; by varnum;

    run;

/*  select variables into a macro variable in correct order*/
    proc sql noprint;
        select name into: ordered_vars separated by " " from vars3 order by varnum;
    quit;

/*  reorder variables*/
    data &dsn._reordered;
        retain &ordered_vars.;
        set &dsn.;
    run;

%mend order;

And then you can use the syntax %order(temp, move_me, before, v1); to create a dataset called temp_reordered that has move_me slotted in before v1. In your case, it sounds like you would want to run %order(myTable, zipFixed, before, [your 8th variable's name]) and then drop any extraneous variables to keep your variables ordered correctly.

Upvotes: 3

Joe
Joe

Reputation: 63434

Zip codes are typically padded with zeros as Tom notes, not with spaces. They also can be three digits in a few cases (for example, Puerto Rico), so be aware of that.

Further, depending on your needs, formatting the column may be sufficient. It won't change the contents of the numeric column, but it will change how it is displayed.

proc datasets;
  modify have;
  format zip z5.;
quit;

Again, for some use cases this won't be helpful, but for others it may be superior to converting to character.

Upvotes: 0

Related Questions