Why re-ordering of variables is not working?

[Moksha]

Hi, I am new to SAS and trying to print the last 10 records of sashelp.class. I am dropping obs from the output and want to bring the currentobs as the first column. But, whatever options that SAS suggests like using of keep, retain, format, informat, length and attrib before set statement are not working in this example. Please, help me understand what is wrong with the code and how to print the variables in the order currentobs, Name, sex, age, height, weight.

Here is the code that I am using:

data lasttenobs ;
if 0 then set sashelp.class nobs=n;
put "no. of observations =" n;
put "no. of observations...... =" n;
startobs=n-9;
/* format currentobs name age sex height weight;*/
/* keep currentobs name age sex height weight;*/
do i= startobs to n by 1 ;
currentobs=i;
/* length currentobs 4;*/

/* format currentobs name age sex height weight;*/

put "current obs=" currentobs;
/* format currentobs name age sex height weight;*/
keep currentobs name age sex height weight;
/* informat currentobs name age sex height weight;*/
set sashelp.class point=i;
put "observation #" i;

/* retain currentobs name age sex height weight;*/
/* output lasttenobs;*/
/* keep currentobs name age sex height weight;*/
output lasttenobs;

drop startobs;
end;
/* keep currentobs name age sex height weight;*/
stop;

/* keep currentobs name age sex height weight;*/

run;
proc print data=lasttenobs noobs;

run;

OUPUT of the above code:

Name Sex Age Height Weight currentobs

John	M	12	59.0	99.5	10
Joyce	F	11	51.3	50.5	11
Judy	F	14	64.3	90.0	12
Louise	F	12	56.3	77.0	13
Mary	F	15	66.5	112.0	14
Philip	M	16	72.0	150.0	15
Robert	M	12	64.8	128.0	16
Ronald	M	15	67.0	133.0	17
Thomas	M	11	57.5	85.0	18
William	M	15	66.5	112.0	19

But, I want the currentobs as the first column. Please, help.

Thank you.

[PaigeMiller · Accepted solution]

Don't do it by re-ordering variables in the data step.

Do this by using the VAR statement in PROC PRINT.

proc print data=lastenobs noobs;
    var currentobs name sex age height weight;
run;

[Cynthia_sas]

Hi:

  In addition, your program seems more complicated than it needs to be. Assuming that SASHELP.CLASS is sorted by name and that all you want are the last 10 observations, you can create the CURRENTOBS variable with a simpler program and then use options FIRSTOBS and OBS to control only getting the last 10 in PROC PRINT, as shown below:

Or, an alternative approach would be to use Macro variables as helper variables to capture the numbers you need to print only the last 10:

Cynthia

[Moksha]

Hi Cynthia, thank you very much for providing alternative methods. But, I didn't understand the method where symputx is used. Can you please, help me understand this?

Thanks

[Cynthia_sas]

Hi:

  CALL SYMPUTX is used to insert Macro variables into a macro variable symbol table because the values stored in the macro variables can be  based on information from reading the SAS dataset -- as in this case where we want to know the starting and stopping obs number for the last 10 observations in the input data. In the first example, I create the CURRENTOBS variable by capturing the _N_ value and then for PROC PRINT, I hard-coded the firstobs= and obs= values in the program. (keep in mind that if the data are sorted differently, not in alpha order, then the CURRENTOBS value for each row would be for a different name.)

  In the second program, I also create CURRENTOBS in the DATA step program. Then, using the END= option allows me to use conditional logic when SAS is handling the last observation in the input data set. In this case, on the last row, I now know the number of obs in the data file and I just need to subtract to get the value for FIRSTOBS. My helper variables are created using CALL SYMPUTX and since I stored the helper macro variables in the Global Symbol Table, they are available throughout this SAS session, until I delete them from the Global Symbol Table or the session ends. That way, they are available to use in the PROC  PRINT step.

  Knowing about and using helper macro variables like this is a useful technique for taking the information from one program and saving it into a macro variable for use in a subsequent step. Of course, SAS Macro processing and the Macro facility can be very, very much more complicated than what I show here, but the amount of code that is used is minimal and if you use the var statement to control the order of the variables for PROC PRINT, you don't have to fiddle with the LENGTH statement to make sure that CURRENTOBS appears first. The VAR statement, to me is a better way to control variable order for report purposes. Fiddling with the order of the variables in internal storage is not really necessary, if the end result is a report. (in my opinion I would rather control the order of variables with a VAR or COLUMN statement instead of relying on the data to be in a certain order when internally stored.)

Cynthia

[Moksha]

Thank you very much Cynthia for a very detailed explanation. As I am new to SAS, I could not understand the symputx function and put(_n_,2.0) calls in the IF condition. I have tried searching google, but somehow I could not get clear clarification on these functions.

if lastone = 1 then do;
call symputx('stop', put(_n_, 2.0), 'G');
call symputx('first', put(_n_-10+1, 2.0), 'G');
end;

Can you please explain about symputx and put functions also?

Thanks 

[Moksha]

Thank you very much Paige Miller. It worked.

[Quentin]

As Paige wrote, typically variable order in a dataset doesn't matter, and it's best to deal with variable order when you use PROC PRINT, or whatever.

But since this a learning exercise, the key point to understand is that variables are added to the program data vector (PDV) in the order in which they are seen by the DATA step compiler.  The first statement of your DATA step is:

if 0 then set sashelp.class nobs=n;

The compiler will see the variables in SASHELP.CLASS, and add them to the PDV in order.  So the first variables in the PDV will be Name Sex Age Height Weight N

If you want the currentobs variable to be ordered first, you could add a LENGTH statement as the first statement:

length currentobs 8;

Note also that your code could be simplified to:

data lasttenobs ;
  put "before any data has been read " ;
  put _all_ /;

  do i=n-9 to n by 1 ;
    currentobs=i ;
    set sashelp.class point=i nobs=n;
    put _all_ ; 
    output lasttenobs;
  end ;
  stop ;
run;

With that code, the variables will be ordered in the PDV as: i n currentobs Name Sex Age Height Weight.

You might be surprised that you can reference the variable N on the first line, even though it isn't assigned a value until the fourth line.  This works because when the SET statement compiles, the nobs= option assigns the value 19 to the variable N.  Thus when the step starts executing, before the SET statement has executed to read the first record, N already has a value.

The PUT _ALL_ ; statement can be used to show the values in the PDV, in order.

1    data lasttenobs ;
2      put "before any data has been read " ;
3      put _all_ /;
4
5      do i=n-9 to n by 1 ;
6        currentobs=i ;
7        set sashelp.class point=i nobs=n;
8        put _all_ ;
9        output lasttenobs;
10     end ;
11     stop ;
12   run;

before any data has been read
i=0 n=19 currentobs=. Name=  Sex=  Age=. Height=. Weight=. _ERROR_=0 _N_=1

i=10 n=19 currentobs=10 Name=John Sex=M Age=12 Height=59 Weight=99.5 _ERROR_=0 _N_=1
i=11 n=19 currentobs=11 Name=Joyce Sex=F Age=11 Height=51.3 Weight=50.5 _ERROR_=0 _N_=1
i=12 n=19 currentobs=12 Name=Judy Sex=F Age=14 Height=64.3 Weight=90 _ERROR_=0 _N_=1
i=13 n=19 currentobs=13 Name=Louise Sex=F Age=12 Height=56.3 Weight=77 _ERROR_=0 _N_=1
i=14 n=19 currentobs=14 Name=Mary Sex=F Age=15 Height=66.5 Weight=112 _ERROR_=0 _N_=1
i=15 n=19 currentobs=15 Name=Philip Sex=M Age=16 Height=72 Weight=150 _ERROR_=0 _N_=1
i=16 n=19 currentobs=16 Name=Robert Sex=M Age=12 Height=64.8 Weight=128 _ERROR_=0 _N_=1
i=17 n=19 currentobs=17 Name=Ronald Sex=M Age=15 Height=67 Weight=133 _ERROR_=0 _N_=1
i=18 n=19 currentobs=18 Name=Thomas Sex=M Age=11 Height=57.5 Weight=85 _ERROR_=0 _N_=1
i=19 n=19 currentobs=19 Name=William Sex=M Age=15 Height=66.5 Weight=112 _ERROR_=0 _N_=1
NOTE: The data set WORK.LASTTENOBS has 10 observations and 6 variables.

[Moksha]

Thank you very much Quentin. Both suggestions using length and the alternative code are helpful.

[Tom]

So you got the advice:

using of keep, retain, format, informat, length and attrib before set statement

But you did not do that.  The first statement of your data step includes a SET statement.

To make CURRENTOBS the first variable just then define it first.

data want;
  currentobs+1;
  set sashelp.class nobs=nobs;
  if currentobs > nobs - 10;
run;

Obs    currentobs    Name       Sex    Age    Height    Weight

  1        10        John        M      12     59.0       99.5
  2        11        Joyce       F      11     51.3       50.5
  3        12        Judy        F      14     64.3       90.0
  4        13        Louise      F      12     56.3       77.0
  5        14        Mary        F      15     66.5      112.0
  6        15        Philip      M      16     72.0      150.0
  7        16        Robert      M      12     64.8      128.0
  8        17        Ronald      M      15     67.0      133.0
  9        18        Thomas      M      11     57.5       85.0
 10        19        William     M      15     66.5      112.0

[Moksha]

Thank you very much Tom.