Transposing to wide format

[bayzid]

How can i transpose the following long data to the wide format?

data long;
input var $ value source;
cards;
aid 45 1
bid 46 1
aid 47 1
bid 48 1
aid 45 2
bid 46 2
aid 47 2
bid 48 2
; run;

data wide;
input aid bid source;
cards;
45 46 1
47 48 1
45 46 2
47 48 2
; run;

[Kurt_Bremser]

Is it always a sequence of "aid" followed by "bid", containing identical values for source?

[bayzid]

The aid and bid may change sequence or we can have more members to add, such as cid. Yes- all the id variables will have identical source.

[Kurt_Bremser]

So you must first define a clear rule for detecting the moment where you write a wide observation and reset the values of RETAINed variables to missing. Without an identifier for corresponding input observations, you can't use PROC TRANSPOSE, you'll need some tricky DATA step programming.

[bayzid]

The data came from appending multiple JSON files. For each source there are same number of aid and bid.

[Kurt_Bremser]

But the issue here is that the combinations of source and var are not unique, so you need another rule which identifies the "groups" that should constitute a single wide observation.

[BayzidurRahman]

Yes- that's the challenge. The id variables are not unique within the group of by variables. Can something be done using array?

[Kurt_Bremser]

Defining the rule must be done by you before we can start to code. You must define the logic first.

[bayzid]

We need to have a row identifier as a combination of var set and source as shown below.

data long;
input var $ value source row;
cards;
aid 45 1 1
bid 46 1 1
aid 47 1 2
bid 48 1 2
aid 45 2 3
bid 46 2 3
aid 47 2 4
bid 48 2 4
; run;

[Kurt_Bremser]

Do you already have that row identifier?

[bayzid]

No.

[Kurt_Bremser]

Then what would be the rule (in plain words) for assigning it?

[bayzid]

As one can see in my last dataset, unique number should be assigned  for each new repeat of the var and source.

[Kurt_Bremser]

This needs some tricky code:

data long;
input var $ value source;
cards;
aid 45 1
bid 46 1
aid 47 1
bid 48 1
aid 45 2
bid 46 2
aid 47 2
bid 48 2
;

proc sql noprint;
select distinct var into :columns separated by " " from long;
quit;
%put &columns;
%macro wide;

data wide;
set long;
by source;
array c {*} &columns;
retain
%do i = 1 %to %sysfunc(countw(&columns));
  %scan(&columns,&i)
%end;
;
if first.source then call missing(of c{*});
select (var);
%do i = 1 %to %sysfunc(countw(&columns));
  when ("%scan(&columns,&i)") do;
    if %scan(&columns,&i) ne .
    then do;
      output;
      call missing(of c{*});
    end;
    %scan(&columns,&i) = value;
  end;
%end;
end;
if last.source and (
%do i = 1 %to %sysfunc(countw(&columns));
  %if &i ne 1 %then or;
  %scan(&columns,&i) ne .
%end;
)
then output;
drop var value;
run;

%mend;

%wide

[bayzid]

Thanks for your time to generate the code. It works for numeric data with short length. But my actual data is a mixture of character and long integer and it doesn't work well.

My data:

Obs var value source row123456789101112

aid	11a3fc66-1f44-4421-bad5-7b0f053a8a19	1	1
bid	1494141000168105	1	1
cid	1494131000168101	1	1
aid	11a3fc66-1f44-4421-bad5-7b0f053a8a19	1	2
bid	1494131000168659	1	2
cid	1494131000168333	1	2
aid	11a3fc66-1f44-4421-bad5-7b0f053a8a19	2	3
bid	2154131000168101	2	3
cid	88794131000168101	2	3
aid	11a3fc66-1f44-4421-bad5-7b0f053a8a19	2	4
bid	1598731000168101	2	4
cid	1489751000168101	2	4

when i use your code the resulting wide table looks like:

Obs source row aid bid cid1234

1	2	.	1.4941E15	1.4941E15
1	2	.	1.4941E15	1.4941E15
2	4	.	2.1541E15	8.8794E16
2	4	.	1.5987E15	1.4898E15

If i use proc transpose with source and row variables in the by statement it works fine. Thus I just need help to generate the row variable.

Obs source row aid bid cid1234

1	1	11a3fc66-1f44-4421-bad5-7b0f053a8a19	1494141000168105	1494131000168101
1	2	11a3fc66-1f44-4421-bad5-7b0f053a8a19	1494131000168659	1494131000168333
2	1	11a3fc66-1f44-4421-bad5-7b0f053a8a19	2154131000168101	88794131000168101
2	2	11a3fc66-1f44-4421-bad5-7b0f053a8a19	1598731000168101	1489751000168101