topic Re: Find the column that will provide the requested value in SAS Programming

Find the column that will provide the requested value

Ronein — Mon, 09 Nov 2020 08:18:50 GMT

Hello

I want to add a new column called "Value" that will get the value of column tYYMM.

For example:

Id=999,mon=t1906,so the value will be from column t1906 (2)

Id=888,mon=t1908,so the value will be from column t1908 (3)

Id=444,mon=t1903,so the value will be from column t1903 (9)

Data Rawtb;
input ID mon $ t1901 t1902 t1903 t1904 t1905 t1906 t1907 t1908 t1909 t1910 t1911 t1912;
cards;
999 t1906 1 3 5 7 9 2 4 6 2 7 9 4
888 t1908 3 5 2 1 6 1 2 3 9 1 8 7
444 t1903 8 5 9 1 4 1 2 6 9 1 2 3
;
run;

Re: Find the column that will provide the requested value

Ronein — Mon, 09 Nov 2020 08:32:30 GMT

I know to do it with long structure but what is the way to do it with wide structure?



Data ttt1;
input ID Criticalmon  ;
cards;
999 1906
888 1908
;
run;

Data ttt2;
input ID mon Y ;
cards;
999 1901 1
999 1902 3
999 1903 5
999 1904 7
999 1905 9 
999 1906 2 
999 1907 4 
999 1908 6
999 1909 12
999 1910 7
999 1911 9 
999 1912 4
888 1901 3
888 1902 5
888 1903 2 
888 1904 1
888 1905 6
888 1906 1
888 1907 2
888 1908 8
888 1909 9
888 1910 1
888 1911 8
888 1912 7
;
run;


PROC SQL;
	create table wanted  as
	select a.*,b.Y as criticalValue 	   
	from  ttt1 as a
	left join ttt2 as b
	on a.ID=b.ID and a.Criticalmon=b.mon
;
QUIT;

Re: Find the column that will provide the requested value

PeterClemmensen — Mon, 09 Nov 2020 08:39:06 GMT

Here is a brute force approach. Simply iterate over all tXXXX variables until you find a match. Then leave the search and set value.

Data Rawtb;
input ID mon $ t1901 t1902 t1903 t1904 t1905 t1906 t1907 t1908 t1909 t1910 t1911 t1912;
cards;
999 t1906 1 3 5 7 9 2 4 6 2 7 9 4
888 t1908 3 5 2 1 6 1 2 3 9 1 8 7
444 t1903 8 5 9 1 4 1 2 6 9 1 2 3
;
run;

data want;
   set Rawtb;
   array t t1901 - t1912;
   do over t;
      if vname(t) = mon then leave;
   end;
   value = t;
run;

Re: Find the column that will provide the requested value

Ksharp — Mon, 09 Nov 2020 11:45:33 GMT

Data Rawtb;
input ID mon $ t1901 t1902 t1903 t1904 t1905 t1906 t1907 t1908 t1909 t1910 t1911 t1912;
cards;
999 t1906 1 3 5 7 9 2 4 6 2 7 9 4
888 t1908 3 5 2 1 6 1 2 3 9 1 8 7
444 t1903 8 5 9 1 4 1 2 6 9 1 2 3
;
run;

data want;
   set Rawtb;
   value = vvaluex(mon);
run;

Re: Find the column that will provide the requested value

PaigeMiller — Mon, 09 Nov 2020 11:59:23 GMT

If you don't create these wide data sets, with year and month in the variable name, and you use a long data set, this is extremely easy to do.

Even if you adopt the very simple solution from @Ksharp , you still had to go through the effort of creating variables t1901 to t1912, which isn't necessary with a long data set.