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.
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.
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.
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.
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.
"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.
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.
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.