topic Re: Base SAS in SAS Programming

Adding a new column to YR1001 data

vishalrajpoot3 — Fri, 07 Sep 2018 14:15:02 GMT

Please help me to understand how can i get a new column added to set sashelp.yr1001. The new column should be the percentage of total of the column of YR1001.

Below is my code, I am getting the below error and everything is written in log window.

ERROR: Array subscript out of range at line 81 column 43.

Code -

proc means data=sashelp.yr1001 noprint;
output out=abc (drop= _ : ) sum= sum_1 - sum_504;
run;

data ab1;
set sashelp.yr1001;
if _n_ = 1 then set abc;
array z(*) s0:;
do i = 1 to dim(z);
if z(i)=. then z(i)=1;
end;
array x(*) p1 - p504;
array y(*) sum:;
do i = 1 to dim(x);
if x(i)=. then x(i)=(y(i)/z(i))/100;
end;
run;

Re: Base SAS

gamotte — Fri, 07 Sep 2018 12:14:54 GMT

Hello,

There are only 503 columns in sashelp.yr1001 whose name begins with "S0" so your array z has one element

less than the others.

Re: Base SAS

vishalrajpoot3 — Fri, 07 Sep 2018 13:06:30 GMT

no, there are 505 variable in YR1001 out of which 504 start with 'S00'.

Re: Base SAS

RW9 — Fri, 07 Sep 2018 13:17:06 GMT

Post test data in the form of a datastep using the code window (it is the {i} above post area), only need a few rows:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

There is no other way we can infer what you are working with. One of the three arrays has not enough elements, most likely one is y as the other two are looped by dim().

Re: Base SAS

gamotte — Fri, 07 Sep 2018 13:26:16 GMT

Not on my computer :

143  proc sql;
144      SELECT count(*) INTO :nvar
145      FROM dictionary.columns
146      WHERE LIBNAME="SASHELP" AND MEMNAME="YR1001"
147      AND NAME LIKE "S0%";
148  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.05 seconds
      cpu time            0.00 seconds


149
150  %put &=nvar.;
NVAR=     503

Try to print the dimensions of each of your arrays to see if they match :

dimz=dim(z);

put dimz=;

Re: Base SAS

vishalrajpoot3 — Fri, 07 Sep 2018 13:39:49 GMT

if i change the array size from 504 to 503 code is working fine. But when i go through the properties of Yr1001 there are 505 variables in it. Is something i am missing.

Name:	YR1001
Description:	M-competition 1001 Series, Annual
Type:	Table
Location:	SASHELP.YR1001
Rows:	126
Columns:	505
Date created:	Aug 10, 2017, 7:35:46 AM
Date modified:	Aug 10, 2017, 7:35:46 AM

Re: Base SAS

gamotte — Fri, 07 Sep 2018 13:41:40 GMT

proc sql;
	SELECT NAME
	FROM dictionary.columns
	WHERE LIBNAME="SASHELP" AND MEMNAME="YR1001"
	AND NAME NOT LIKE "S0%";
	;
quit;

SAS Output

Column Name
T
S1000

Re: Base SAS

RW9 — Fri, 07 Sep 2018 13:42:13 GMT

The output provided tells me very little. There may be 505 columns, but what are those columns. Post test data in the form of a datastep:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: Base SAS

RW9 — Fri, 07 Sep 2018 13:44:28 GMT

Can I ask, how do you know this, there is no YR1001 dataset in SASHELP on my install? Hence why I keep asking for test data.

Re: Base SAS

gamotte — Fri, 07 Sep 2018 13:53:06 GMT

There is such a dataset on my SAS install. I just discovered its existence so i cannot tell you more than that.

Edit : Sas 9.4 / windows 7

Re: Base SAS

vishalrajpoot3 — Fri, 07 Sep 2018 13:50:28 GMT

I am using SAS Studio and SAS University Edition, in libraries ,in both the option this YR1001 is there.

Re: Base SAS

vishalrajpoot3 — Fri, 07 Sep 2018 13:53:13 GMT

there is 505 column and only first column is 'T' and rest 504 starts with 'S0'.

Re: Base SAS

RW9 — Fri, 07 Sep 2018 13:53:29 GMT

Yep, not on mine. I think this clearly shows why test data should be screen one when posting questions in future, I will push this as suggestion for the forums.

Re: Base SAS

gamotte — Fri, 07 Sep 2018 13:55:56 GMT

No, S1000 begins with "S1".

Maybe you want to modify your z array definition to

array z(*) s:;

Re: Base SAS

vishalrajpoot3 — Fri, 07 Sep 2018 14:00:18 GMT

Ohhh yes, that was the only issue.

Thanks

Re: Base SAS

ChrisHemedinger — Fri, 07 Sep 2018 14:13:52 GMT

Looks like this is solved now -- but just to explain why some people have YR1001 and others don't. yr1001.sas7bdat is distributed with SAS/ETS, and at least parts of ETS are included in SAS University Edition.

Re: Base SAS

art297 — Fri, 07 Sep 2018 15:39:47 GMT

@ChrisHemedinger and @vishalrajpoot3: I don't think this is solved yet.

The initial proc means was:

proc means data=sashelp.yr1001 noprint;
  output out=abc (drop= _  : ) sum= sum_1 - sum_504;
run;

That will create sums for T and s0001--s0996. I don't think that is what was wanted.

The following, I think, does accomplish the task correctly:

proc means data=sashelp.yr1001 (drop=t) noprint;
  output out=abc sum= xsum_1 - xsum_504;
run;

data ab1 (drop=xsum: i);
  set sashelp.yr1001;
  if _n_ = 1 then set abc;
  array z(*) xsum:;
  do i = 1 to dim(z);
    if missing(z(i)) then z(i)=1;
  end;
  array x(*) p1 - p504;
  array y(*) s:;
  do i = 1 to dim(x);
    if x(i)=. then x(i)=(y(i)/z(i))/100;
  end;
run;

Art, CEO, AnalystFinder.com

Re: Base SAS

vishalrajpoot3 — Thu, 13 Sep 2018 19:52:57 GMT

Thanks

When you assigning 1 for missing value, why this is not showing in output data set.

below is the code I came with as solution-

proc means data=sashelp.yr1001 noprint;

output out=abc (drop= _ : ) sum= add_1 - add_504;

run;

data ab1 (drop=add:);

set sashelp.yr1001;

if _n_ = 1 then set abc;

array z(*) S:;

do i = 1 to dim(z);

if z(i)=. then z(i)=1;

end;

array x(*) p1 - p504;

array y(*) add_:;

do i = 1 to dim(x);

if x(i)=. then x(i)=(z(i)/y(i))*100;

end;

run;