turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Transpose

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-05-2017 05:08 PM

Hi Friends,

I have a data set like below (input) and need your help in getting the output dataset.

data input;

input ColumnA $ ColumnB $ ColumnC $ ColumnD$ Value;

cards;

A B C A1 1

A B C A1 2

A B C A1 3

A B C A1 4

A B C A2 1

A B C A2 2

A B C A2 3

A B C A2 4

;

output:

data output;

input ColumnA $ ColumnB $ ColumnC $ A1 A2;

cards;

A B C 1 1

A B C 2 2

A B C 3 3

A B C 4 4

;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sascodequestion

02-05-2017 05:20 PM - edited 02-05-2017 05:23 PM

YOu have not sufficiently described the rules by which you want to associate the data records.

- Are you associating records within columna/columnb/columnc groups?
- Are you pairing columnd='A1' and columnd='A2' records?
- If so, are they associated by position within a/b/c?
- Do you always expect the same number of A1's and A2's?

Please try to avoid mindreading requirements.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to mkeintz

02-05-2017 05:40 PM

Hi,

Thanks for your response.

1.The total number of observations should be devided into 2 groups, which is based on the values of the coulmnD(A1 and A2)

2. The observations should be grouped based on ColumnA,columnB, ColumnC and columnD

3. The Values in the columnD should become new coulmns, for example in the above example we have two different values A1 and A2 so we should get 2 new columns in the output called A1 and A2

3. Column A1 and A2 no need to have same rows. The total number of observation are going to be equal to the higher number of observations in any group. For example if group A1 contains 5 and A2 contains 3, so total number of obs will be equal to 5 and column A2 will have values 0.

Hope this is clear

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sascodequestion

02-05-2017 06:10 PM

You would have to create new variables to account for what you want. Given your example you could use something like:

data input; input ColumnA $ ColumnB $ ColumnC $ ColumnD $ Value; cards; A B C A1 1 A B C A1 2 A B C A1 3 A B C A1 4 A B C A2 1 A B C A2 2 A B C A2 3 A B C A2 4 ; data need; set input; if mod(_n_,4) eq 0 then x=4; else x=mod(_n_,4); run; proc sort data=need; by ColumnA ColumnB ColumnC x ColumnD; run; data need; set need; if mod(_n_,2) eq 1 then group+1; run; proc transpose data=need out=want (drop= group _name_); by ColumnA ColumnB ColumnC Group; var value; id ColumnD; run;

HTH,

Art, CEO, AnalystFinder.com

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to sascodequestion

02-06-2017 12:55 AM

Assuming (1) your data are sorted by columna/columnb/columnc and (2) columnd='A1' or 'A2', then you can use a MERGE process as below:

```
data want (drop=n n1 n2 columnd);
n1=0; n2=0;
do until (last.columnc);
set input (keep=columna columnb columnc columnd);
by columna columnb columnc;
if columnd='A1' then n1=n1+1;
else if columnd='A2' then n2=n2+1;
end;
do N=1 by 1 until (last.columnc);
merge input (in=in1 where=(columnd='A1') rename=(value=a1))
input (in=in2 where=(columnd='A2') rename=(value=a2));
by columna columnb columnc;
if n>n1 then a1=0;
if n>n2 then a2=0;
output;
end;
run;
```

Note that N1 and N2 are the counts of A1 and A2 records respectively.