topic Re: Present one row per observation in New SAS User

Present one row per observation

Aleksandar — Wed, 17 Apr 2019 16:54:04 GMT

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?

Re: Present one row per observation

PaigeMiller — Wed, 17 Apr 2019 16:55:16 GMT

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-data-AKA-generate/ta-p/258712

Re: Present one row per observation

Aleksandar — Wed, 17 Apr 2019 17:12:32 GMT

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

Re: Present one row per observation

novinosrin — Wed, 17 Apr 2019 17:18:09 GMT

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!

Re: Present one row per observation

Astounding — Wed, 17 Apr 2019 17:47:31 GMT

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.

Re: Present one row per observation

Aleksandar — Wed, 17 Apr 2019 18:35:01 GMT

Thank you very much,

it helped a lot.

Re: Present one row per observation

novinosrin — Wed, 17 Apr 2019 21:43:20 GMT

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

Re: Present one row per observation

Aleksandar — Thu, 18 Apr 2019 08:13:47 GMT

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?

Re: Present one row per observation

Astounding — Thu, 18 Apr 2019 12:03:09 GMT

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?