topic Re: Number of observation in dataset in SAS Programming

Number of observation in dataset

aloou — Wed, 19 Oct 2022 09:49:49 GMT

Hello,

I have a table that may contain 1 to 5 observations, i need to add a column that contains the number of observations.

i tried row_num= _n_ but it is not what i want.

i will be thankful for your help.

Re: Number of observation in dataset

PeterClemmensen — Wed, 19 Oct 2022 09:55:52 GMT

You want the number of observations in every row, correct?

If so

data want;
   set sashelp.class nobs = nobs;
   n = nobs;
run;

Re: Number of observation in dataset

aloou — Wed, 19 Oct 2022 10:02:18 GMT

No not the number of observations in every row.
like for example the table contains 5 observations , i want the new column to be like :
1
2
3
4
5

Re: Number of observation in dataset

PeterClemmensen — Wed, 19 Oct 2022 10:03:36 GMT

Then your initial attempt is good

data want;
   set sashelp.class;
   n = _N_;
run;

Re: Number of observation in dataset

aloou — Wed, 19 Oct 2022 10:07:18 GMT

i tried it again in a table with two observations, it writes
1
1

Re: Number of observation in dataset

PeterClemmensen — Wed, 19 Oct 2022 10:17:18 GMT

Show me your code?

The _N_ variable gives you the number of times the data step has iterated. Not the input observation number.

Re: Number of observation in dataset

aloou — Wed, 19 Oct 2022 10:22:19 GMT

data casuser.test;
set CASUSER.CHOIXC;
n = _N_;
run;
i need to put the observation number and not how many times it iterated.

Re: Number of observation in dataset

Rick_SAS — Wed, 19 Oct 2022 12:35:48 GMT

From your code, it looks like you might be running this DATA step in CAS. In CAS tables, there are no "observation numbers" because there is not an intrinsic order to the rows. They can be processed in any order.

In Viya 4, you can use the ADDROWID=YES option to add a special variable called _ROWID_

SAS Help Center: ADDROWID= Data Set Option

In earlier versions of CAS, you can combine the _N_ and the _THREADID_ variables to get a unique ID. See

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/casdspgm/p1myr5r0z9n55vn1c0ijy5wt6f8g.htm#n1fgpzk1m0yisyn1i1cnsu1143rg

and

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.4/casdspgm/p10ux48pz7qknzn1ux1bs48vplnx.htm#p1o13jlo1hed7kn1qhb24c3pvd7i

Re: Number of observation in dataset

Quentin — Wed, 19 Oct 2022 13:26:18 GMT

Update: I think the below will not work on CAS running multi-threaded, i.e. I think it would result in duplicate values for N. See e.g. https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/casdspgm/p10ux48pz7qknzn1ux1bs48vplnx.htm#n05jzo5a7djwphn1neq15j5m50iw . Perhaps if you added /single=yes to force it to run single-threaded it would work. And perhaps adding /single=yes to the original code using _N_ would work.

___

I haven't tried CAS... if you just want a counter, maybe the sum statement would be an option?

data casuser.test;
  set CASUSER.CHOIXC;
  n + 1 ;
run;

Would N be unique in that case, or would the multi-threading result in there being duplicate values? I understand that there is no intrinsic ordering, so if you ran the step multiple times, you might get different results.

Re: Number of observation in dataset

Patrick — Wed, 19 Oct 2022 12:55:15 GMT

@Quentin I can see in the docu for Viya that the RETAIN statement is supported https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lestmtsref/p0t2ac0tfzcgbjn112mu96hkgg9o.htm

Really curious if that will cause CAS to execute in a single thread or if it means the data step will execute in SPRE. @Rick_SAS Do you know?

Re: Number of observation in dataset

Quentin — Wed, 19 Oct 2022 13:23:42 GMT

Thanks @Patrick. I just found this example in the docs:

https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/casdspgm/p10ux48pz7qknzn1ux1bs48vplnx.htm#n05jzo5a7djwphn1neq15j5m50iw

Which I think answers your question and mine:

When you use a retained variable to hold a sum in a multithreaded DATA step, the variable contains only the sum of the values that the processing thread can see.

In order to sum a variable across an entire dataset, they use retain in a first data step that runs multithreaded, and then run a second data step and force it to be single-threaded.

I haven't thought much about Viya, since I don't have it. But I guess if you're used to thinking about the data step having a PDV, when it's multi-threaded it sounds like each thread has it's own PDV.

So in the first step of the example:

data mycas.sums;                     
  retain homeowners_sum;                 /* 1 */
  keep homeowners_sum;                   /* 2 */
  set mycas.purchase end = done;         /* 3 */
  if demog_ho = 1 then                          
     homeowners_sum + 1;                 /* 4 */
  if done then output;                   /* 5 */
run;

If this were running on SAS 9 or on Viya single-threaded, it would output one record. But on CAS it looks like it outputs one record per thread. Because each thread will have its own end= flag in its PDV. I guess. : )

My understanding is when you submit code on Viya, Viya will decide whether a step will run on CAS or SPRE/Compute Server. Kind of like implicit pass-through deciding whether to run a query in SAS or remote database. But I generally don't like guessing where my code will run. Do you know if there is a system option I can run in a Viya session that will say "please run all this code on SPRE/Compute Server"?

I'm looking forward to upcoming Ask the Expert on converting SAS 9 code to Viya, hopefully it will address some of these differences. https://communities.sas.com/t5/Upcoming-Events/How-Do-I-Modify-SAS-9-Programs-to-Run-in-SAS-Viya/ec-p/838684#M355