BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jwillis
Quartz | Level 8

I have attached my code and the log.  I am trying to build formats from a CNTLIN dataset just to change a character value based on a numeric id value.  I want to be able to change the values automatically every time the job runs and I thought a format would be the best method.

1 ACCEPTED SOLUTION

Accepted Solutions
jwillis
Quartz | Level 8

I had a combination of issues in the data step that created the cntlin datasets.  The code below solved my problems.


proc sql; drop table WORK.changethese3; quit;
DATA work.CHANGETHESE3(KEEP=FMTNAME START END LABEL min max hlo) ;
    LENGTH FMTNAME $ 8 START 5 LABEL 8;
retain type "N" FMTNAME "chgid"  min 3 max 8;
    set work.changethese2 END=LASTREC;
if _n_ = 1 then do;
     START=0;
     END=(ID-1);
        LABEL=0;
  OUTPUT;
end;
    START=ID;
END=ID;
    LABEL=1;
    OUTPUT;
     IF LASTREC THEN DO;
        LABEL=0;
     START=(ID + 1); END=999;
     hlo = '0';
        OUTPUT;
   END;
run;

PROC FORMAT CNTLIN=WORK.changethese3;
QUIT;

proc format fmtlib;
select chgid;
QUIT;

proc sql; drop table WORK.changethese4; quit;
DATA work.CHANGETHESE4(KEEP=FMTNAME START END LABEL hlo min max OTHER) ;
    LENGTH FMTNAME $ 8 START 5 LABEL $1;
    set work.changethese2 END=LASTREC;
     retain FMTNAME "chgrsk" min 1 max 5;
   OTHER = ' ';
    START = ID;
END = ID;
    LABEL = riska;
    OUTPUT;
    IF LASTREC THEN DO;
      START=.;
   END = .;
      LABEL=' ';
   hlo = '0';
      OUTPUT;
      END;
run;

PROC FORMAT CNTLIN=WORK.changethese4;
run;


proc format fmtlib;
select chgrsk;
run;             


1596  data work.world;    /* unprojected */
1597         length chekid 8 chekrisk $1;
1598         format chekid 5. chekrisk $1. chekid2 chgid.;
1599     set work.worldx;
1600    chekid = put(id,chgid.);
1601    chekid2 = id;
1602     if chekid = 1 then do;
1603                chekrisk = put(id,chgrsk.);
1604               riska=put(id,chgrsk.);
1605               put id= riska=;
1606   end;
1607    if _n_ < 5 then do;   put "first four rows" id= chekid= chekid2=  chekrisk =; end;
1608    drop chekid chekrisk chekid2;
1609  run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      1600:12
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
ID=399 riska=2
ID=573 riska=1
ID=573 riska=1
ID=573 riska=1
NOTE: There were 45805 observations read from the data set WORK.WORLDX.
NOTE: The data set WORK.WORLD has 45805 observations and 9 variables.

View solution in original post

7 REPLIES 7
Reeza
Super User

What's the type of the variable ID?

Also, you haven't included an other in your proc format so the value passed through is the same 72 which doesn't map to anything.

jwillis
Quartz | Level 8

The variable, ID, is a numeric variable.  When I encounter a matching ID value (numeric value representing the country ID in the MAPS.WORLD SAS dataset) in my dataset that has a matching country ID, I want to change the character variable, RISKA, to a character value of "1","2","3","4" or "5". The ID numeric value of 72 comes from the MAPS.WORLD dataset.  I do not understand where the value of "*" is being read from or created from.  My attempts to add an "OTHER" line to the numeric format, have not worked.

Kurt_Bremser
Super User

You get the star because chekid2 has been assigned the format chgid. and that format does not cover the value 72.

Also variable chekid has been declared numeric (by length and format statement) and is used to assign a character value (the result of the put(id,chgid.)), which leads to an implicit type conversion that fails when the put yields the star.

Note the 'NOTE: Character values have been converted to numeric values at'; in a clean program, this is not acceptable.

jwillis
Quartz | Level 8

"Note the 'NOTE: Character values have been converted to numeric values at'; in a clean program, this is not acceptable."  Correct, that is why I am asking for help.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Looking at the format statement:

START,           END‚               LABEL                  

        .,                  .‚                      0                                                

  399‚             399‚                      1                                                  

  573‚             573‚                      1             

There is no result for 72.  Hence the format returns a "*", i.e. the statement chekid = put(id,chgid.);, is trying to assign an asterix "*" which means no value found, into a numeric variable which cannot handle the non-numeric data.  In your format statements put an Other = -99 (or some value) so that values which do not fir into your windows return a numeric value.

Tom
Super User Tom
Super User

Your HLO variable should be character and you should set it to the letter O when you are defining the value for the OTHER category.

jwillis
Quartz | Level 8

I had a combination of issues in the data step that created the cntlin datasets.  The code below solved my problems.


proc sql; drop table WORK.changethese3; quit;
DATA work.CHANGETHESE3(KEEP=FMTNAME START END LABEL min max hlo) ;
    LENGTH FMTNAME $ 8 START 5 LABEL 8;
retain type "N" FMTNAME "chgid"  min 3 max 8;
    set work.changethese2 END=LASTREC;
if _n_ = 1 then do;
     START=0;
     END=(ID-1);
        LABEL=0;
  OUTPUT;
end;
    START=ID;
END=ID;
    LABEL=1;
    OUTPUT;
     IF LASTREC THEN DO;
        LABEL=0;
     START=(ID + 1); END=999;
     hlo = '0';
        OUTPUT;
   END;
run;

PROC FORMAT CNTLIN=WORK.changethese3;
QUIT;

proc format fmtlib;
select chgid;
QUIT;

proc sql; drop table WORK.changethese4; quit;
DATA work.CHANGETHESE4(KEEP=FMTNAME START END LABEL hlo min max OTHER) ;
    LENGTH FMTNAME $ 8 START 5 LABEL $1;
    set work.changethese2 END=LASTREC;
     retain FMTNAME "chgrsk" min 1 max 5;
   OTHER = ' ';
    START = ID;
END = ID;
    LABEL = riska;
    OUTPUT;
    IF LASTREC THEN DO;
      START=.;
   END = .;
      LABEL=' ';
   hlo = '0';
      OUTPUT;
      END;
run;

PROC FORMAT CNTLIN=WORK.changethese4;
run;


proc format fmtlib;
select chgrsk;
run;             


1596  data work.world;    /* unprojected */
1597         length chekid 8 chekrisk $1;
1598         format chekid 5. chekrisk $1. chekid2 chgid.;
1599     set work.worldx;
1600    chekid = put(id,chgid.);
1601    chekid2 = id;
1602     if chekid = 1 then do;
1603                chekrisk = put(id,chgrsk.);
1604               riska=put(id,chgrsk.);
1605               put id= riska=;
1606   end;
1607    if _n_ < 5 then do;   put "first four rows" id= chekid= chekid2=  chekrisk =; end;
1608    drop chekid chekrisk chekid2;
1609  run;

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).
      1600:12
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
first four rowsID=72 chekid=0 chekid2=0 chekrisk=
ID=399 riska=2
ID=573 riska=1
ID=573 riska=1
ID=573 riska=1
NOTE: There were 45805 observations read from the data set WORK.WORLDX.
NOTE: The data set WORK.WORLD has 45805 observations and 9 variables.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 2688 views
  • 4 likes
  • 5 in conversation