logo
down
shadow

Transpose and Create Variables


Transpose and Create Variables

By : Simon
Date : October 21 2020, 08:10 AM
I hope this helps . If you have an known, or maximum number of observations, you can use a PIVOT in concert with Row_Number() (assuming TIME is the proper sequence)
If the number of observations/columns is not known, then you'll have to use some dynamic SQL.
code :
Select *
 From  (
        Select A.[PT_ID] 
              ,B.*
         From  (Select *,RN=Row_Number() over (Partition By PT_ID Order by Time) From YourTable ) A
         Cross Apply (values (concat('Time' ,RN),[Time])
                            ,(concat('HR'   ,RN),[HR])
                            ,(concat('RR'   ,RN),[RR])
                            ,(concat('SysBP',RN),[SysBP])
                     ) B(Item,Value)
       ) src
 Pivot (sum(Value) for Item in  ([Time1],[HR1],[RR1],[SysBP1]
                                ,[Time2],[HR2],[RR2],[SysBP2]
                                ,[Time3],[HR3],[RR3],[SysBP3]
                                ,[Time4],[HR4],[RR4],[SysBP4]
                                ) ) pvt


Share : facebook icon twitter icon
transpose with multiple variables in SAS

transpose with multiple variables in SAS


By : KumarMohit
Date : March 29 2020, 07:55 AM
To fix the issue you can do You're not going to get there with PROC TRANSPOSE without a lot of extra work. Most of the time, Wide to Long is easier handled within the data step. In this case you can do it very easily with arrays.
code :
data have;
input id lab1 $ lab2 $ lab3 $ dt1 $ dt2 $ dt3 $ 
      bili1 $ bili2 $ bili3 $ alb1 $ alb2 $ alb3 $;
datalines;
 3  dx   sx   sx   2/04 2/06 3/08 x.x   x.x   x.x   x.x  x.x  x.x 
 4  dx   tx   tx   5/05 3/06 9/06 x.x   x.x   x.x   x.x  x.x  x.x 
;;;;
run;

data want;
set have;
array labs lab1-lab3;
array dts dt1-dt3;
array bilis bili1-bili3;
array albs alb1-alb3;

do _t = 1 to dim(labs);
  lab = labs[_t];
  dt  = dts[_t];
  bili= bilis[_t];
  alb = albs[_t];
  output;
end;
keep id lab dt bili alb;
run;
SAS transpose with more then 100 variables

SAS transpose with more then 100 variables


By : jalal
Date : March 29 2020, 07:55 AM
Does that help See following code which uses two PROC TRANSPOSE steps and 1 DATA STEP + 1 PROC SORT to acheive the output you are looking for. This could more probably be done in a single DATA step using ARRAYS. But, the following approach should be within reach of most SAS programmers.
code :
/*sample data*/
data have;
INPUT ID MODEL $ A_2013 A_2014 A_2022 
B_2013 B_2014 B_2022
;
datalines;
1 NEW 0.5 0.2 0.1 0.4 3.4 12.89
2 OLD 0.5 0.2 0.3 0.4 3.4 12.5
3 NEW 0.5 0.2 0.1 0.2 3.4 12.4
4 OLD 0.5 0.2 4.1 0.4 3.4 12.0
5 NEW 0.2 0.1 0.3 0.4 3.1 12.1
6 OLD 2.5 1.2 2.1 1.4 2.4 1.2
;
run;
/*1st transpose: turn the input dataset from short and wide to long and thin*/
proc transpose data = have out = have_t(rename = (col1=value));
by id model;
run;

/*split the labels into two parts, segment & year*/
data have_t2;
length segment $8 year 8;
set have_t;
underScorePosition=index(_name_,'_');
segment = substr(_name_,1, underScorePosition-1);
year = input(substr(_name_,underScorePosition+1), 4.);
drop _name_;
run;
/*make sure values are properly sorted before passing to 2nd transpose*/
proc sort data = have_t2;
by id model year;
run;
/*2nd transpose: here turn  sgements into columns*/
proc transpose data = have_t2 out = want(drop =_name_);
by id model year;
id segment;
var value;
run;
ID  MODEL   year    A   B
1   NEW 2013    0.5 0.4
1   NEW 2014    0.2 3.4
1   NEW 2022    0.1 12.89
2   OLD 2013    0.5 0.4
2   OLD 2014    0.2 3.4
2   OLD 2022    0.3 12.5
3   NEW 2013    0.5 0.2
3   NEW 2014    0.2 3.4
3   NEW 2022    0.1 12.4
4   OLD 2013    0.5 0.4
4   OLD 2014    0.2 3.4
4   OLD 2022    4.1 12
5   NEW 2013    0.2 0.4
5   NEW 2014    0.1 3.1
5   NEW 2022    0.3 12.1
6   OLD 2013    2.5 1.4
6   OLD 2014    1.2 2.4
6   OLD 2022    2.1 1.2
Proc Transpose SAS: Transpose and Rename Variables

Proc Transpose SAS: Transpose and Rename Variables


By : joela03
Date : March 29 2020, 07:55 AM
I wish did fix the issue. You need to make it in the form (by variables) (variable name) (variable value)
In this case that means six rows per person, with name varname value.
code :
data have_pret;
 set have;
 varname=cats('y',x);
 value=y;
 output;
 varname=cats('z',x);
 value=z;
 output;
 keep name value varname;
run;
proc transpose data=have_pret out=want;
 by name;
 id varname;
 var value;
run;
PROC TRANSPOSE: VAR 'all variables'?

PROC TRANSPOSE: VAR 'all variables'?


By : user6234017
Date : March 29 2020, 07:55 AM
Hope that helps I have this simple proc transpose: , After taking data null's advice I used
code :
_ALL_ 
_CHARACTER_ 
_NUMERIC_ 
Transpose table by two variables

Transpose table by two variables


By : Tejesh
Date : March 29 2020, 07:55 AM
it fixes the issue There could indeed be a way to do it with one proc transpose but I'm not seeing it.
Alternatevely, you can rather easily do it with two proc transpose and a data step merge:
code :
data begin; 
 input MAKName $ MLOName $ tcode $ Count Percent;
 cards;
 ABARTH  124     Miss  5     5.1 
 ABARTH  124     Hit   94    94.9 
 FIAT    124     Miss  30    12 
 FIAT    124     Hit   220   88 
;
run;

proc transpose data=Begin out= count_test(drop=_name_) prefix=Count_;
  by makname mloname; 
  var  count; 
  id tcode; 
run;
proc transpose data=Begin out= percent_test(drop=_name_) prefix=Percent_;
  by makname mloname; 
  var  percent; 
  id tcode; 
run;
data want;
  merge count_test percent_test;
  by makname mloname;
run;
data begin; 
 input MAKName $ MLOName $ tcode $ Count Percent;
 cards;
 ABARTH  124     Miss  5     5.1 
 ABARTH  124     Hit   94    94.9 
 FIAT    124     Miss  30    12 
 FIAT    124     Hit   220   88 
;
run;

proc transpose data=Begin out=test;
  by makname mloname;
  var  count percent; 
  id tcode; 
run;

data want (drop=_name_);
merge test(where=(_name_='Count') rename=(Miss=Count_miss Hit=Count_hit))
      test(where=(_name_='Percent') rename=(Miss=Percent_miss Hit=Percent_hit));
by makname mloname;
run;
Related Posts Related Posts :
  • T-SQL query with aggregates returning a non empty result set when predicate is false
  • Difference between top 2 over multiple rows
  • SQL Server 2016 SSIS Conditionally Overwrite Text File
  • The column was specified multiple times
  • is there something faster than Enumerable.Except<TSource> Method?
  • I need to take database back up database name with date
  • Numeric to money conversion fails for bigger value when using (DT_CY) type cast in derived column component
  • SQL server stored procedure insert with dynamic exec
  • Select * from x where column is quarter
  • How to grant unsafe assembly to a username found dynamically?
  • How can I loop though columns by name in an SSIS Script component?
  • SSRS Export to Excel Filter To Many Tabs
  • SQL Server 2012 Management Studio hangs
  • Can NOLOCK cause DISTINCT to fail?
  • SSRS Mobile Reports Distinct Count
  • Append api data to Amazon AWS SQL Server database
  • Merge columns together from multiples tables, columns not in order
  • Why Can't I See SQL Server Compact After I Install It?
  • SQL Server - Where Like statement with variable returning no results
  • SQL query to find non-matching fields in certain rows
  • SQL Server - Select between 2 dates of type DD/MM/YYYY
  • SQL Geography Datatype , checking Point in Polygon , STIntersects producing wrong result
  • A positional parameter cannot be found that accepts argument
  • Array var in SQL Server 2008
  • Using a variable in a sqsh script is not working while querying in sql server
  • Error Executing Query String
  • SQL view with substring in WHERE clause?
  • Specify Xml index storage location in Sql Server
  • How to push Spark dataframe to Sql Server table when set_identity is off?
  • Create index error -The object name contains more than the maximum number of prefixes. The maximum is 2
  • T-SQL: Minimum number of characters to remove/replace so that one string is substring of another
  • sql query that gets the difference between 2 recent rows for every row item that occurs more than once in a table
  • SSIS conditionally import if source is available
  • How to set SQL user password when deploying database project using CI/CD pipeline
  • SQL: How can I Parse firstname, lastname and title from fullname?
  • Should I store XML declaration in database and return using WebApi
  • SSIS Foreach loop error on CSV files
  • Azure SQL as Publisher
  • SSRS 2016 Mobile Reports Error with missing dataset
  • SQL 2005: Optimize upsert-like Stored Procedure using Cursor, possible?
  • SQLSERVER - How to list Procs created by a particular user?
  • SSIS Expression to get last day of the month given YYYYMM
  • how to sum up all time values on a given date?
  • Text Clause Explanation
  • SQL Server 2016 - Can't to log into SSMS after install
  • SQL Server table with different case in PowerShell SMO vs SSMS
  • SQL : Output multiple left joined rows as one record for csv file
  • Removing Date From Date/Time Column
  • Subquery a Cast Function
  • Set based stacking update query
  • Can someone explain the `BypassPrepare` connection-parameter used when building an ETL via SSIS and VS?
  • What is the optimal way to get only latest ID's from table in SQL
  • How to join JSON with table?
  • Transforming a sql table into its inverse
  • Azure SQL Database Pricing
  • Log Reader error while adding a node in SQL Server peer to peer replication
  • Correct way to convert Month Name and Year to complete date SQL Server
  • Select records that appear twice, with a slight difference
  • tsql display value on first row of each group
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org