topic Re: Converting column names to row value with condition in SAS Programming

Converting column names to row value with condition

nid197 — Thu, 05 Sep 2019 09:20:43 GMT

Hi ,
I have a dataset
Viewnm tablenam thrcolname updtcoln flg1 upflg2 crflg3 qrflg4
Nm tabl1 accno acrnbr y n n n
Nm2 tbr2 accno crmnbr n n y n
Mn3 tbr2 accno crmnrcln n y n n
Nm4 tbr2 enitynr entynbr n n n y

I want to convert this to-

seqno refflg uptcol
1 flg1 acrnbr
1 upflg2 crmnbr
2 crflg3 crmnrcln
3 qrflg4 entynbr

Can anypne pls help

Re: Converting column names to row value with condition

PeterClemmensen — Thu, 05 Sep 2019 09:25:08 GMT

I don't follow this logic. Eg how are the seqno determined? Why is Seqno=1 in the first two obs?

Please be more specific about how you get from your data now to your desired results

Re: Converting column names to row value with condition

PeterClemmensen — Thu, 05 Sep 2019 09:48:18 GMT

If I understand you correctly though

data have;
input (Viewnm tablenam thrcolname updtcoln flg1 upflg2 crflg3 qrflg4)($);
datalines;
Nm tabl1 accno acrnbr y n n n
Nm2 tbr2 accno crmnbr n n y n
Mn3 tbr2 accno crmnrcln n y n n
Nm4 tbr2 enitynr entynbr n n n y
;

data want;
    format seqno refflg updtcoln;
    set have;
    array _{4} flg1--qrflg4;
    do seqno=1 to dim(_);
        put seqno;
        refflg=vname(_[seqno]);
        output;
    end;
    keep seqno refflg updtcoln;
run;

Re: Converting column names to row value with condition

nid197 — Thu, 05 Sep 2019 09:55:33 GMT

Seq 1 in first 2 obs denotes that it is table 1's 1st variable.same is with table2.first variable again starts with 1 2 3

Re: Converting column names to row value with condition

nid197 — Thu, 05 Sep 2019 09:57:55 GMT

Seq no is again a new variable which i want to create based on first.value of table name and view name

Re: Converting column names to row value with condition

Jagadishkatam — Thu, 05 Sep 2019 11:01:07 GMT

Please try the below code

data have;
input (Viewnm tablenam thrcolname updtcoln flg1 upflg2 crflg3 qrflg4)($);
datalines;
Nm tbr1 accno acrnbr y n n n
Nm2 tbr2 accno crmnbr n n y n
Mn3 tbr2 accno crmnrcln n y n n
Nm4 tbr2 enitynr entynbr n n n y
;

proc sort data=have;
by tablenam;
run;

data want;
set have;
by tablenam;
retain seqno;
if first.tablenam then seqno=1;
else seqno+1;
run;

proc sort data=want;
by seqno ;
run;

data want2;
set want;
by seqno;
array vars(*) $ flg1 upflg2 crflg3 qrflg4;
do i = 1 to dim(vars);
if _n_=i then refflg=vname(vars(i));
end;
keep seqno refflg updtcoln;
run;