BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
samira
Obsidian | Level 7

Hello,

 

I have a question regarding sas data step modification. lets make my question clear using the following figure: 

 

Untitled.png

 

as you can see I have a data set similar to table (a) and I want to add a zero at the very first cell in the table (table (b)) and then shift all the cells. in the next step I need to sum up the coressponding cell values in new table (table(a)+table(b)=table(c) ). hope mt explanation is clear.

I am very new in SAS and I would be appriciated if somebody help me. 

 

Thank you,

Samira

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

You can ask your company to buy IML license , it is very cheap .

And for your such Matrix Operator , the best way do is IML code. 

If you insist to use Data Step, that will lead you to a lot more code.

 

data a;
input _2006 _2007 _2008;
cards;
1 5 1
1 5 2
3 2 2
4 3 2
;
run;
proc transpose data=a(obs=0) out=temp;
run;
data _null_;
 set temp end=last;
 if _n_=1 then call execute('data all;set ');
 call execute(catt('a(keep=',_name_,' rename=(',_name_,'=var))'));
 if last then call execute(';run;');
run;
data all;
 set all;
 new_var=sum(var,lag(var));
 drop var;
run;
%let dsid=%sysfunc(open(a));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
data vname;
 set temp;
 do i=1 to &nobs;
  output;
 end;
 drop i;
run;
data combine;
 merge vname all;
run;
proc transpose data=combine out=temp1;
 by _name_;
run;
proc transpose data=temp1 out=want(drop=_name_);
run;

View solution in original post

6 REPLIES 6
samira
Obsidian | Level 7

Hello,

 

I have a question regarding sas data step modification. lets make my question clear using the following figure: 

 

Untitled.png

 

as you can see I have a data set similar to table (a) and I want to add a zero at the very first cell in the table (table (b)) and then shift all the cells. in the next step I need to sum up the coressponding cell values in new table (table(a)+table(b)=table(c) ). hope mt explanation is clear.

I am very new in SAS and I would be appriciated if somebody help me. 

 

Thank you,

Samira

ballardw
Super User

Since the values you show, 2006, 2007 and 2008 are not valid SAS variable names can you provide the names of your actual variables?

 

You say that you insert a zero, which is pretty clear for 2006 but is the "insert" for 2007 actually the bottom value for 2006? and the "insert" for 2008 the last 2007 value? And will there always be only 4 values in each column, such as quarters of a year?

samira
Obsidian | Level 7

Thank you for your reply,

 

actually I just wanted to make my dataset simple. here is my actual dataset: 

 

Untitled.png

 

You say that you insert a zero, which is pretty clear for 2006 but is the "insert" for 2007 actually the bottom value for 2006? yes that is correct  

 

and the "insert" for 2008 the last 2007 value? yes that is also correct 

And will there always be only 4 values in each column, such as quarters of a year? there will be always 53 rows and 12 columns (includs week)

 

 

Ksharp
Super User

It is about Matrix operator, Plz post it at IML forum.

 

data a;
input _2006 _2007 _2008;
cards;
1 5 1
1 5 2
3 2 2
4 3 2
;
run;
proc iml;
use a;
read all var _num_ into x[c=vnames];
close;
y=shapecol(0||remove(shapecol(x,0,1),nrow(x)#ncol(x)),0,ncol(x));
z=x+y;

print x[l='A' c=vnames],y[l='B' c=vnames],z[l='C' c=vnames];
quit;
samira
Obsidian | Level 7
Ksharp, thank you very much for your response. at this time I am not be able to use IML procedure because we do not have it in our SAS licence. is there any other way to do this? such as array in data step?
Ksharp
Super User

You can ask your company to buy IML license , it is very cheap .

And for your such Matrix Operator , the best way do is IML code. 

If you insist to use Data Step, that will lead you to a lot more code.

 

data a;
input _2006 _2007 _2008;
cards;
1 5 1
1 5 2
3 2 2
4 3 2
;
run;
proc transpose data=a(obs=0) out=temp;
run;
data _null_;
 set temp end=last;
 if _n_=1 then call execute('data all;set ');
 call execute(catt('a(keep=',_name_,' rename=(',_name_,'=var))'));
 if last then call execute(';run;');
run;
data all;
 set all;
 new_var=sum(var,lag(var));
 drop var;
run;
%let dsid=%sysfunc(open(a));
%let nobs=%sysfunc(attrn(&dsid,nlobs));
%let dsid=%sysfunc(close(&dsid));
data vname;
 set temp;
 do i=1 to &nobs;
  output;
 end;
 drop i;
run;
data combine;
 merge vname all;
run;
proc transpose data=combine out=temp1;
 by _name_;
run;
proc transpose data=temp1 out=want(drop=_name_);
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1049 views
  • 1 like
  • 3 in conversation