topic Re: Data wrangle query in SAS Programming

Data wrangle query

dennis_oz — Thu, 22 Oct 2020 22:54:39 GMT

Hi all,

I wasn't clear on my previous question and had assumed proc transpose was the way to go .

I will delete that post and re-post below .

I haven't posted the whole data , but my columns in the have are about 500 columns .

Similarly about 500 rows.

Please find atttached a Sample-Dimension1.csv with the data that needs to be flipped .

Please let me know if I need to provide further information.

Have

FI	101 Capital Region R Uwgt	101 Capital Region R 000s
Health Products Use Vitamins tablets capsules	66	64
Health Products Use Anti smoking products	5	5
Anti smoking prdcts Freq Buy Every 2 to 3 months	0	0
Anti smoking prdcts Freq Buy Every 4 to 6 months	0	0
Weight Loss Remedies Freq Buy Every 2 to 3 months	3	5
Weight Loss Remedies Freq Buy Every 4 to 6 months	0	0

Want

F1	Health Products Use Vitamins tablets capsules	Health Products Use Anti smoking products	Anti smoking prdcts Freq Buy Every 4 to 6 months	Weight Loss Remedies Freq Buy Every 2 to 3 months	Weight Loss Remedies Freq Buy Every 4 to 6 months
101 Capital Region R Uwgt	66	5	0	3	0
101 Capital Region R 000s	64	5	0	5	0

Re: Data wrangle query

Kurt_Bremser — Thu, 22 Oct 2020 21:27:11 GMT

First, transpose your 500 columns to a long format BY fi.

Then, run a PROC REPORT with fi as an ACROSS column.

For code suggestions, provide your dataset in a data step with datalines that works and creates a dataset with valid SAS names.

Re: Data wrangle query

data_null__ — Thu, 22 Oct 2020 21:31:54 GMT

Please provide better HAVE data in the form of a data step with cards data.

data have;
   infile cards expandtabs;
   input FI&$64. a b;
   id + 1;
   label a='101 Capital Region R Uwgt' b='101 Capital Region R 000s';
   cards;
Health Products Use Vitamins tablets capsules	66	64
Health Products Use Anti smoking products	 5	 5
Anti smoking prdcts Freq Buy Every 2 to 3 months   0	0
Anti smoking prdcts Freq Buy Every 4 to 6 months	0	0
Weight Loss Remedies Freq Buy Every 2 to 3 months	3	5
Weight Loss Remedies Freq Buy Every 4 to 6 months	0	0
;;;;
proc print;
   run;

proc transpose data=have out=want(drop=_name_) prefix=FI label=F1;
   var a b;
   id id;
   idlabel fi;
   run;
proc contents varnum;
proc print;
   run;
proc print label;
   label f1=' ';
   run;

Re: Data wrangle query

dennis_oz — Thu, 22 Oct 2020 23:03:44 GMT

Thanks much !! it works like a charm !!