topic Re: Transposing to wide format in SAS Programming

Transposing to wide format

bayzid — Wed, 26 Apr 2023 00:22:21 GMT

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;

Re: Transposing to wide format

Kurt_Bremser — Wed, 26 Apr 2023 05:34:53 GMT

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

Re: Transposing to wide format

bayzid — Wed, 26 Apr 2023 05:38:03 GMT

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.

Re: Transposing to wide format

Kurt_Bremser — Wed, 26 Apr 2023 05:55:52 GMT

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.

Re: Transposing to wide format

bayzid — Wed, 26 Apr 2023 06:07:13 GMT

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

Re: Transposing to wide format

Kurt_Bremser — Wed, 26 Apr 2023 06:12:32 GMT

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.

Re: Transposing to wide format

BayzidurRahman — Wed, 26 Apr 2023 06:18:23 GMT

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

Re: Transposing to wide format

Kurt_Bremser — Wed, 26 Apr 2023 06:21:47 GMT

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

Re: Transposing to wide format

bayzid — Wed, 26 Apr 2023 07:11:56 GMT

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;

Re: Transposing to wide format

Kurt_Bremser — Wed, 26 Apr 2023 07:37:58 GMT

Do you already have that row identifier?

Re: Transposing to wide format

bayzid — Wed, 26 Apr 2023 08:23:49 GMT

No.

Re: Transposing to wide format

Kurt_Bremser — Wed, 26 Apr 2023 08:51:14 GMT

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

Re: Transposing to wide format

bayzid — Wed, 26 Apr 2023 09:33:03 GMT

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

Re: Transposing to wide format

Kurt_Bremser — Wed, 26 Apr 2023 12:34:15 GMT

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

Re: Transposing to wide format

bayzid — Wed, 26 Apr 2023 13:33:32 GMT

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

Re: Transposing to wide format

Tom — Wed, 26 Apr 2023 15:54:35 GMT

What is the RULE that says when to wrap them?

Do you just want to take pairs?

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
; 

data long_pairs;
  set long;
  group + (1=mod(_n_,2));
run;

proc transpose data=long_pairs out=wide(drop=_name_);
  by group source;
  id var;
  var value;
run;

proc print;
run;

OBS    group    source    aid    bid

 1       1         1       45     46
 2       2         1       47     48
 3       3         2       45     46
 4       4         2       47     48

Re: Transposing to wide format

Reeza — Wed, 26 Apr 2023 16:19:14 GMT

Go back and check the JSON files. Maybe you shouldn't be working with the all data files but different files. Or there should be something that identifies each group uniquely. Or if it's from different files you can add in each file name.

Re: Transposing to wide format

bayzid — Wed, 26 Apr 2023 20:27:30 GMT

The file name is being saved in the Master_&group datasets but that is not enough to uniquely identify the rows to reshape into wide. The JSON files do not have any column as row identifier but it appears on the left of each file as shown below (1-6).

We need to find some way to import that information from each file as a new column in the Master_&group dataset.

Re: Transposing to wide format

Reeza — Wed, 26 Apr 2023 20:48:20 GMT

In JSON the brackets denote the series of information and group it, which is why I'm suggesting you look at the json mapped data and see if a different file has the data in the format you need.

Those numbers are just line numbers in the text editor. And if you changed the view you could change the line numbers. It's not something you can rely on.

Re: Transposing to wide format

bayzid — Wed, 26 Apr 2023 20:51:48 GMT

The rule is each appearance of all the distinct values in var by source. If we have 3 distinct values in var then the last digit needs to be 3 to convert into long_pair. Thus, we need to find out the number of distinct values in "var" for each dataset.

data long;
input var $ value $36. source row;
cards;
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
; run;

data long_pairs;
  set long;
  group + (1=mod(_n_,3));
run;

proc transpose data=long_pairs out=wide(drop=_name_);
  by group source;
  id var;
  var value;
run;

I am trying to avoid to find out the number of distinct values.