topic Re: Looking up diagonal Data in SAS Programming

Looking up diagonal Data

CMANOJ622 — Mon, 23 Sep 2024 14:13:04 GMT

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

Re: Looking up diagonal Data

ChrisNZ — Tue, 24 Sep 2024 16:23:24 GMT

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

Re: Looking up diagonal Data

Ksharp — Wed, 25 Sep 2024 02:20:08 GMT

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;