Need to take a 4 column dataset with the top row being one variable and make it 2 columns

LavendarCat — Mon, 29 Jan 2024 04:23:40 GMT

I am just learning SAS and the assignment is to take a dataset of a certain format and turn it into another format. We are using SAS Studio.

Example:

Black	Gray	Red	White
57	59	60	78
67	76	81	50
52	62	47	66
74	68	68	60
55	64	24	59

Needs to look like:

Color	Number
Black	57
Gray	59
Red	60
White	78
Black	67
Gray	76
Red	81
White	50

I have this so far but am stuck on what to put next. All the practicing we have done in class has been with datasets where they all read in rows where each row is related to each individual variable for that column. Not like here where the Color variable is at the top row and should be repeated with each number in that column. Any hints will be greatly appreciated.

data Problem_3;
infile '/home/u63754936/STAT 725/Homework#1/Data Files HW1/Dataset3.txt';
input Color $ Number;

run;
proc print data=Problem_3;
title 'Problem_3';
run;

Re: Need to take a 4 column dataset with the top row being one variable and make it 2 columns

Patrick — Mon, 29 Jan 2024 04:57:20 GMT

Not sure where you are at in your learning journey so not sure if below code is already fitting for where you are at.

If you have an external text file then one way is to create the desired table structure as part of your data step that reads the table.

data want;
  infile '/home/u63754936/STAT 725/Homework#1/Data Files HW1/Dataset3.txt'
    firstobs=2
    dsd
    dlm=' '
    truncover
    ;    
  length color $5;
  do color='Black','Grey','Red','White';
    input number @;
    output;
  end;
run;

proc print data=want;
run;

You will likely have to add a few options to the infile statement that instruct SAS how to read the external file - like for example dlm=.... to define the delimiting character used to separate the values.

If the do loop hasn't been covered yet in your classes then here another coding option.

data want;
  infile '/home/u63754936/STAT 725/Homework#1/Data Files HW1/Dataset3.txt'
    firstobs=2
    dsd
    dlm=' '
    truncover
    ;    
  length color $5;
  color='Black';
  input number @;
  output;
  color='Grey';
  input number @;
  output;
  color='Red';
  input number @;
  output;
  color='White';
  input number ;
  output;
run;

The most important bit in this code is the @ at the end of the input statement that allows you to use multiple input statements without the cursor jumping onto the next line of input. Look up the details in the SAS docu.

topic Need to take a 4 column dataset with the top row being one variable and make it 2 columns in New SAS User

Need to take a 4 column dataset with the top row being one variable and make it 2 columns

Re: Need to take a 4 column dataset with the top row being one variable and make it 2 columns