selecting the good value from a proc format

[alepage]

I am not very familliar with proc format..

and I would like to solve my problem.

how can I use the value of rank into a dataset and assign the proper value of 100 to Temp1;

proc format ;
value PtPerfo
1=120
2=110
3=100
4=80
5=70
6=50
7=40
8=34
9=32
10=30
;
run;

The code of Data Temp work fine.  But not the one of Data Temp2.

%let T=3;
Data Temp;
Temp1=%sysfunc(putn(&T,Ptperfo));
run;
Data Temp2;
rank=3;
Temp1=%sysfunc(putn(rank,Ptperfo));
run;

[Astounding · Accepted solution]

It's a happy accident that the TEMP step is working.  %SYSFUNC should hardly ever be used to process data.  Here is a start at fixing the DATA step:

Data Temp2;
rank=3;
Temp1=put(rank,Ptperfo.);
run;

The PUT function generates a character string, however.  If you want TEMP1 to be a numeric variable, you will need to use:

Data Temp2;
rank=3;
Temp1=input( put(rank,Ptperfo.), 3.) ;
run;

[alepage]

I have test the following code and it works:

proc format ;
value PtPerfo
1=120
2=110
3=100
4=80
5=70
6=50
7=40
8=34
9=32
10=30
;
run;
Data Temp;
input Classement 8.;
datalines;
1
2
3
4
5
6
7
8
9
10
;
run;
Data Temp2;
set Temp;
Pointage=input(put(Classement,PtPerfo.),best12.);
run;

But When I am using the following code it is not working.  I have no error message but it generate a new classement variable (empty) and pointage is empty also??  Any idea how to solve that?

Data performance_AgentVente3;
set performance_AgentVente3;
Pointage=input(put(Classement,PtPerfo.),best12.);
run;

Performance_AgentVente3 look like:

...Classement     Pointage    classement

1                            .                   .

2                            .                    .

3                             .                   .

and so on....

[Astounding]

Where did "best12." come from?  It should not be used here.  It is a format, not an informat.  Fix that first, then we can investigate how you got an extra column.

****** EDITED:

Also note, the program you posted that creates the data set TEMP2 works as is (despite the use of best12.).  The problem somehow occurs when you switch to a different data set, not using TEMP as the source data.

[ballardw]

---

@alepage wrote:

I have test the following code and it works:

proc format ;
value PtPerfo
1=120
2=110
3=100
4=80
5=70
6=50
7=40
8=34
9=32
10=30
;
run;
Data Temp;
input Classement 8.;
datalines;
1
2
3
4
5
6
7
8
9
10
;
run;
Data Temp2;
set Temp;
Pointage=input(put(Classement,PtPerfo.),best12.);
run;

But When I am using the following code it is not working.  I have no error message but it generate a new classement variable (empty) and pointage is empty also??  Any idea how to solve that?

Data performance_AgentVente3;
set performance_AgentVente3;
Pointage=input(put(Classement,PtPerfo.),best12.);
run;

Performance_AgentVente3 look like:

...Classement     Pointage    classement

1                            .                   .

2                            .                    .

3                             .                   .

and so on....

---

Was the variable classement in the data set performance_AgentVente3 before you ran the code? If not then by referencing it you added it to the data and without values it is missing. And since it is missing the result of most operations with it would also be missing.

Please note that use of

Data performance_AgentVente3;
   set performance_AgentVente3;

is a very good way to create problems for yourself,  especially when testing new coding methods. By replacing the data set every time you change the code you may change values, lose variables, add things and not know when that happened.

[alepage]

I agree with you that best12. is not the best format to choose.  I have change it to 3

Moreover, I have made some test with my data at home (using sas university edition)

First, my excel file contains the data from the sas dataset Data performance_AgentVente1.  I have exported this table using sas enterprises guide 7.1 into an excel file.

When I have read this file at home, I was unable to calculate the variable pointage using the proc format.

Then I have open my excel file, I have deleted the column classement and I have made a new one named Rank

I have entered manualy the data (1 2 3 1 2 3 and so on..) and I have save my excel file

After those actions, I was able to create the variable Pointage using the following code.

It tells me that my sas table was either corrupted or the way the variable Classement was created is unapropriate.

When I will come back tomorrow to the office, I will send you the piece of code I have been using to create the variable Classement.

It could be that?

Thanks for your comments

/** Import an XLSX file. **/

PROC IMPORT DATAFILE="/folders/myshortcuts/Study1/classeur1.xlsx"
OUT=WORK.MYEXCEL
DBMS=XLSX
REPLACE;
RUN;

/** Print the results. **/

proc format ;
value PtPerfo
1=120
2=110
3=100
4=80
5=70
6=50
7=40
8=34
9=32
10=30
;
run;
proc format ;

value PtPerfoATO
1=120
2=60
3=30
;

run;
Data Temp1 ;
set work.myexcel ;
Pointage=input(put(Rank, PtPerfoATO.), 3.);
run;
PROC PRINT DATA=WORK.Temp1; RUN;