proc format

[AncaTilea]

Arthur's code is great.

It didn't run because there is a missing ";" after &missings.

data test;

  input (x y) ($);

  cards;

1z400 this1

4 45

7 .

8isnt 0900

;

proc sql noprint;

  select catt("_",name,"=input(",name,",?? 32.);",

              "if not missing(_",name,") then do;",

              name,"='numb';end;") into :missings

    separated by " "

      from dictionary.columns

        where libname="WORK" and

            memname="TEST"

  ;

quit;

data want (drop=_:);

  set test;

  &missings.;

run;

proc freq data=want;

  tables _all_;

run;

[Robert_Bardos]

(I'm referring to my earlier reply)

So if you want numbers identified with the hard coded string 'number' then simply change

if not notdigit(compress(your_char_var,' .')) and

     not missing(input(your_char_var,?? 8.)) then

      your_char_var = compress("'"!!your_char_var!!"'") ;

to

if not notdigit(compress(your_char_var,' .')) and

     not missing(input(your_char_var,?? 8.)) then

      your_char_var = 'number' ;

BTW: the "table" as you refer to PROC PRINT's output was intended as an aid in following the logic.

[silasskovsbo]

Then I do not understand what goes wrong.

I have attached my log when I try to run Arthur's program, can you see what goes wrong?

[AncaTilea]

The problem is the in the bolded line you use lowercase instead of uppercase.

You use this

proc sql noprint;

           select catt("_",name,"=input(",name,",?? 32.);",

               "if not missing(_",name,") then do;",

               name,"='numb';end;") into :missings

           separated by " "

               from dictionary.columns

                   where libname="work" and memname="test"

           ;

:

Instead of this

proc sql noprint;

  select catt("_",name,"=input(",name,",?? 32.);",

              "if not missing(_",name,") then do;",

              name,"='numb';end;") into :missings

    separated by " "

      from dictionary.columns

        where libname="WORK" and  memname="TEST"

  ;

quit;

Please try it and let us know 🙂

[silasskovsbo]

Perfect :smileygrin:

Now I get the perfect results...

Thank you all very much for the help!

[AncaTilea]

Excellent!

Now we can all move on!

🙂

[silasskovsbo]

Sorry, Anca 😕

There is still one small question.. This program works perfectly for all character variables, but in my data set there are also a lot of numeric variables.These should not be affected, but with the SQL-program they are made to missing.

So I want:

have

x(cha) y(cha) z(num)

1z400 this1 3

4 45 4

7 . 5

8isnt 0900 6

want

1z400 this1 3

numb numb 4

numb . 5

8isnt numb 6

But now the result is:

1z400 this1 .

numb numb .

numb . .

8isnt numb .

If I can get want, I can just format the numeric observations with a format statement.

[AncaTilea]

The problem is that I don't know how to modify the code that it tells SAS, hey do this only if I tell you so...

I've been trying to use type = "2'...but it doesn't recognize it.

data test;

  input (x y) ($) z;

  cards;

1z400 this1 3

4 45 4

7 . 5

8isnt 0900 6

;

/*add a merge variable for later*/

data test;set test; merge = 1;run;

/*use proc contents to get the type of variables, character or numeric*/

proc contents data = test out = t(keep = NAME type);run;

proc transpose data = t out = t_t prefix= type_; id name ;var type;run;

data t_t;set t_t;merge = 1;run;

proc format;

    value typ 1 = "Numeric" 2 = "Character";

quit;

/*merge with original data set, now we their type*/

data temp(drop = merge);

    merge test t_t(drop = _NAME_ _LABEL_);

    by merge;

    format type_: typ.;

/*hard code just so I can see what happens, and because I don't have time to make it general*/

    _x = input(x, ?? 32.);

    _y = input(y, ?? 32.);

    _z = input(z, ?? 32.);

/*if the variable of interest is not missing and it is not numeric then do*/

if _x ne . & type_x ne 1 then new_x = "numb";else new_x = x; /*but if it is either missing or numeric, then keep the original value*/

if _y ne . & type_y ne 1 then new_y = "numb";else new_y = y;

if _z ne . & type_z ne 1 then new_z = "numb";else new_z = z;

run;

proc freq data = want;

     tables new_:;

run;

OK, the SAS experts will modify this code into a beautiful piece.

I stop here, so I get some work done.

Cheers and best of luck!

[Haikuo]

Well, I admitted I haven't read through the thread, therefore my comments could be off. However, seems to me that one small tweak on Art's code would do it for you, added statement is bold:

data test;

  input (x y) ($) Z;

  cards;

1z400 this1    1

4 45 2

7 .    3

8isnt 0900 4

;

proc sql noprint;

  select catt("_",name,"=input(",name,",?? 32.);",

              "if not missing(_",name,") then do;",

              name,"='numb';end;") into :missings

    separated by " "

      from dictionary.columns

        where libname="WORK" and

            memname="TEST" AND TYPE EQ 'char'

  ;

quit;

data want (drop=_:);

  set test;

  &missings.

run;

proc freq data=want;

  tables _all_;

run;

Haikuo

[AncaTilea]

Yes! I kept saying where type = 2 instead of 'char'.

Thank you, Hai.Kuo!

[silasskovsbo]

Problem solved! Thank you very much all of you...

Very grateful! Best from Aarhus!