Hi All,
I am having a SAS dataset and I need extract to only diagonal data point from the data set and print the same one line. Please help me with SAS code if any.
For ex:
FROM | TO | Jun-24 | Jul-24 | Aug-24 | Sep-24 | Oct-24 | Nov-24 | Dec-24 | Jan-25 | Feb-25 | Mar-25 | Apr-25 | May-25 | Jun-25 |
BALANCE_(01) | BALANCE__(02) | 0 | 0 | 23 | 25 | 26 | 25 | 22 | 25 | 28 | 25 | 20 | 28 | 21 |
BALANCE_(02) | BALANCE__(03) | 0 | 0 | 0 | 26 | 23 | 27 | 20 | 23 | 28 | 26 | 22 | 26 | 27 |
BALANCE__(03) | BALANCE__(04) | 0 | 0 | 0 | 0 | 24 | 22 | 22 | 24 | 24 | 23 | 20 | 23 | 22 |
BALANCE__(04) | BALANCE__(05) | 0 | 0 | 0 | 0 | 0 | 23 | 26 | 22 | 20 | 25 | 20 | 23 | 20 |
BALANCE__(05) | BALANCE__(06) | 0 | 0 | 0 | 0 | 0 | 0 | 26 | 25 | 20 | 27 | 23 | 21 | 21 |
BALANCE__(06) | BALANCE__(07) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 26 | 26 | 27 | 25 | 21 | 21 |
BALANCE__(07) | BALANCE__(07) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 20 | 25 | 27 | 20 | 26 |
BALANCE__(07) | BALANCE__(08) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 25 | 22 | 26 | 23 |
BALANCE__(08) | BALANCE__(09) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 28 | 28 | 22 |
BALANCE__(09) | BALANCE__(10) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 28 | 26 |
BALANCE__(10) | BALANCE__(11) | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 24 |
Result | Jun-24 | Jul-24 | Aug-24 | Sep-24 | Oct-24 | Nov-24 | Dec-24 | Jan-25 | Feb-25 | Mar-25 | Apr-25 | May-25 | Jun-25 | |
Balance | 0 | 0 | 23 | 26 | 24 | 23 | 26 | 26 | 20 | 25 | 28 | 28 | 24 |
I suppose you got the data from Excel. Storing data in variable names is usually a bad idea.
This does what you want:
data HAVE;
input JUN_24 JUL_24 AUG_24 SEP_24 OCT_24 NOV_24;
cards;
0 0 1 11 21 31
0 0 0 2 22 32
0 0 0 0 3 23
0 0 0 0 0 4
run;
data WANT;
array IN[6] JUN_24 JUL_24 AUG_24 SEP_24 OCT_24 NOV_24;
array OUT[6];
retain OUT: 0;
set HAVE end=LASTOBS;
do I=1 to dim(IN);
if IN[I] then do;
OUT[I]=IN[I];
leave;
end;
end;
if LASTOBS then output;
keep OUT:;
rename OUT1=JUN_24 OUT2=JUL_24 OUT3=AUG_24 OUT4=SEP_24 OUT5=OCT_24 OUT6=NOV_24;
run;
JUN_24 | JUL_24 | AUG_24 | SEP_24 | OCT_24 | NOV_24 |
---|---|---|---|---|---|
0 | 0 | 1 | 2 | 3 | 4 |
It is SAS/IML things.
options validvarname=v7;
proc import datafile='C:\Users\xiakeshan\Downloads\Test_1.xlsx' out=have dbms=xlsx replace;run;
proc iml;
use have;
read all var _num_ into num[c=vname1];
close;
vname=putn(inputn(compress(vname1,'_'),'best.')+'01jan1900'd-2,'monyy.');
n=j(1,ncol(num),.);
do i=1 to ncol(num);
if isempty(loc(num[,i]^=0)) then m=1;
else m=max(loc(num[,i]^=0));
n[i]=num[m,i];
end;
create want from n[c=vname];
append from n;
close;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.