Present one row per observation

[Aleksandar]

Hi everyone,

I have my dataset and I am having troubles with organizing it. Namely, I have different variables that are stored in different rows for the same participant. But the trick is that these two variables relate to the same participant (according to his id number). What is the possible way to present or to transpose the variables so that I will have for each participant (according to his id) variables in the same row? I mean, for each participant to have all values of his corresponding variables within the same row?

[PaigeMiller]

Show us a portion of your data, using this method: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[Aleksandar]

What I basically have is id_in_group that represents participant and different variables are stored in different rows with the same id_in_group. I want to create for corresponding participant (id__in_group) just one row where all variables that relate to him will be stored in only one row. What I have now is the same participant stored in many rows just where each row represent one variable related to him. The code that I have run was the following (here I include just one variable):

PROC TRANSPOSE DATA=Session2.Combined_datasets OUT=Session2.Combined_datasets_just_in_rows;
ID IP2000_2years_D;
BY id_in_group;
VAR IP2000_2years_D;
RUN;

Could you please give me some advice on how to solvve this problem?

Thank you

[novinosrin]

Hi @Aleksandar  Welcome to SAS communities. What we need is

1. A sample data of what you HAVE (paste here as text for us to copy paste and work with it)

2. A sample data of what you WANT(your expected output for the input sample)

3. A brief explanation of the convert/business logic and that is your objective

That;s all we need. If you could provide the above 3, somebody will help you with the much needed solution. Thank you!

[Astounding]

It's a little bit of a guess, but here is what I think you are looking for:

proc sort data=have;
   by id_number;
run;

data want;
update have (obs=0) have;
by id_number;
run;

Theoretically, there is some question as to what should happen if you have different values for the same variable on different observations.  This program takes the last non-missing value found for each variable.

[Aleksandar]

Thank you very much,

it helped a lot. 

[novinosrin]

HI @Aleksandar   Glad to note @Astounding  could understand your question and offer the correct solution. Could you please that solution as accepted and close the thread as answered. Thank you and enjoy your time in SAS communities

[Aleksandar]

Actually I have a small question. Your last sentence attracted my attention. The issue is that I have participants with the same id_group_number and therefore I need to include in some cases (for some and not all) two values per variable. What is the possible way to solve that problem?

[Astounding]

Yes, but not using such a simple program. The first step is for you to decide what the solution should look like. In your design, be sure to show the right outcome for both one numeric and one character variable with multiple values.

One preliminary question: is it important for you to understand the solution, or is it only important that it works?