exactly @Tom , it is UTF-8, I think you are right about the number, n, before the month's format does not work😀 %put encoding=%sysfunc(getoption(encoding)); 69 %put encoding=%sysfunc(getoption(encoding)); encoding=UTF-8 70 ... View more

Hi @Tom , thanks a lot for your information! I got this encoding information through the proc content output, and it is unicode: it looks like both %3b and %3B works, which display 3 character month name and full month name respectively, and perhaps what does not work is the number, 3, or I would like to say that if one only wants to display a 3 character month name or a full month name, the number, 3, is not needed. I tried the example again: proc format; picture dateh (default=13) low-high='%d%b%Y:%HH' (datatype=datetime); run; title 'Storms with Category 5 Winds'; proc print data=pg3.storm_detail noobs; var Name ISO_time Wind Pressure Region; where Wind>155; format ISO_time dateh.; run; title;   proc format; picture dateh (default=13) low-high='%d%B%Y:%HH' (datatype=datetime); run; title 'Storms with Category 5 Winds'; proc print data=pg3.storm_detail noobs; var Name ISO_time Wind Pressure Region; where Wind>155; format ISO_time dateh.; run; title;   ... View more

Hi @Kathryn_SAS , sorry I forgot to tell that the code was from PG3 lesson 5's demo, p305d01, and it uses dataset pg3.storm_detail, according to the instruction, the complete code for this is as follows: proc format; picture dateh (default=13) low-high='%d%3B%Y:%HH' (datatype=datetime); run; title 'Storms with Category 5 Winds'; proc print data=pg3.storm_detail noobs; var Name ISO_time Wind Pressure Region; where Wind>155; format ISO_time dateh.; run; title; and I got this output: however, the correct output in the materials is like this: and I can only get this output (above) after I change the format as %d%3b%Y, the code and output is as follows: proc format; picture dateh (default=13) low-high='%d%3b%Y:%HH' (datatype=datetime); run; title 'Storms with Category 5 Winds'; proc print data=pg3.storm_detail noobs; var Name ISO_time Wind Pressure Region; where Wind>155; format ISO_time dateh.; run; title; ... View more

Hi @Kathryn_SAS , the material is SAS Programming 3: Advanced Techniques Course Notes (PDF document), which I downloaded from the 7-days free trial, the version is 15May2019, LWPG3M6_001. Perhaps this mistake is only in the course note PDF and it is not in the online materials. The screen capture of the PDF page is as follows: ... View more

Hi @SASPreK ,your requirement should be done in several steps using different techniques, it would not be able to achieve in a single step through a single technique (e.g., I tried many times with retain and if.first /if.last techniques but failed and almost give up this question and then switched to other techniques). The steps basically include the follows: (a) produce the final results column according to your requirements, this can be done with proc transpose and array, and (b) output the last id and year row together with the final results column, this can be done though if.last  within a data step and then a sql join. The code and results are as follows. data have; input id $ dt:yymmdd10. year test $ result $; format dt date11.; datalines; ABCDE 20100429 2010 T1 N ABCDE 20100712 2010 T1 N ABCDE 20101025 2010 T1 Y ABCDE 20110509 2011 T1 Y ABCDE 20110909 2011 T1 Y ABCUK 20150301 2015 T2 Y ABCUK 20150801 2015 T1 N ABCUK 20150914 2015 T1 N ABCGH 20190613 2019 T1 N ABCGH 20190815 2019 T2 N ABCPK 20130409 2013 T1 N ABCPK 20131021 2013 T1 N ; run; proc print data=have;run; /*produce final results column according to requirements, using transpose and array*/ proc sort data=have out=have1; by id dt; run; proc print data=have1;run; proc transpose data=have1 out=have2; by id; var result; run; proc print data=have2;run; data have3; set have2; array rst[5] $ col1-col5; do i=1 to 5; if rst[i]='Y' then result='Y'; end; run; proc print data=have3;run; /*output the last id and year row together with the final results, using if.last in a data step and sql join*/ data have4; set have1; by id year; if last.year; drop result; run; proc print data=have4;run; proc sql; select h4.*, h3.result from have4 as h4 left join have3 as h3 on h4.id=h3.id; quit;   ... View more

The last table somehow changed the order of the time column. So order by should be used here: proc sql; select h.*, w1.group from have as h left join want1 w1 on h.patient=w1.patient order by h.patient,h.time; quit; ... View more

Hi @ArdigenSupport , I copied my answer to @pink_poodle 's question in the other post here. The basic idea is: (1) first use lag() function or proc transpose, and then cat() function to create the patient group column, and then (2) use sql join to produce the final table. And BTW, I think lag() function and proc transpose are suitable techniques for dealing with data from this kind of typical crossover or repeated measure design.   (1) use lag() function data have1; input patient time drug $; datalines; 1 1 A 1 2 B 2 1 B 2 2 A ; run; proc print data=have1;run; data want1; set have1; by patient; drugprev1=lag1(drug); if first.patient then drugprev1=' '; if last.patient then group=cat(compress(drugprev1), compress(drug)); run; proc print data=want1;run; proc sql; create table patientgrp as select distinct patient,group from want1 where group is not null; select * from patientgrp; quit; proc sql; create table wantfinal as select w.patient,w.time,pg.group from want1 as w left join patientgrp as pg on w.patient=pg.patient; select * from wantfinal; quit;   (2) use proc transpose data have; input patient time drug $; datalines; 1 1 A 1 2 B 1 3 B 2 1 B 2 2 A ; run; proc print data=have;run; data have1; set have; timechar=cat('t',put(time,1.)); run; proc print data=have1;run; proc transpose data=have1 out=want; by patient; id timechar; var drug; run; proc print data=want;run; data want1; set want; group=compress(cat(t1,t2,t3)); run; proc print data=want1;run; proc sql; select h.*, w1.group from have as h left join want1 w1 on h.patient=w1.patient order by h.patient,h.time; quit;   ... View more

Hi @pink_poodle , the complete steps are as follows (using proc transpose to produce the group column): data have; input patient time drug $; datalines; 1 1 A 1 2 B 1 3 B 2 1 B 2 2 A ; run; proc print data=have;run; data have1; set have; timechar=cat('t',put(time,1.)); run; proc print data=have1;run; proc transpose data=have1 out=want; by patient; id timechar; var drug; run; proc print data=want;run; data want1; set want; group=compress(cat(t1,t2,t3)); run; proc print data=want1;run; proc sql; select h.*, w1.group from have as h left join want1 w1 on h.patient=w1.patient; quit;   ... View more

Hi @pink_poodle , after tried many times using if first. and retain etc. techniques and failed, I suddenly realized that this is a very simple problem about proc transpose, the code is as simple as follows : data have; input patient time drug $; datalines; 1 1 A 1 2 B 1 3 B 2 1 B 2 2 A ; run; proc print data=have;run; proc transpose data=have out=want; by patient; id time; var drug; run; proc print data=want;run; And then you can use cat() function to put column 1,2,3 together to produce the patient group column of this typical cross over design. The conclusion is: proc transpose is the simple and efficient solution. ... View more

Hi @pink_poodle , I did not understand your question correctly in my previous thread. Here is the correct steps: (1) use lag() function and cat() function to create the group column, (2) use proc sql to produce correspondence patient and group table, and (3) use sql join to produce final table which fill in the correspondence group for each patient. The code and result tables are as follows.    My solution looks take much more steps than @Ksharp 's solution (that you pick as the solution for the question), but I think that solution consumes a lot difficult thinking and the logic behind the code is very difficult to follow. My solution looks take more steps, but the thinking behind it is very simple. What do you think? Please kindly let me know if my code answer your question, 😀thanks! data have1; input patient time drug $; datalines; 1 1 A 1 2 B 2 1 B 2 2 A ; run; proc print data=have1;run; data want1; set have1; by patient; drugprev1=lag1(drug); if first.patient then drugprev1=' '; if last.patient then group=cat(compress(drugprev1), compress(drug)); run; proc print data=want1;run; proc sql; create table patientgrp as select distinct patient,group from want1 where group is not null; select * from patientgrp; quit; proc sql; create table wantfinal as select w.patient,w.time,pg.group from want1 as w left join patientgrp as pg on w.patient=pg.patient; select * from wantfinal; quit; ... View more

Hi @pink_poodle , would a simple proc sql step solve your question? The code and results are as follows. data want; input patient time drug $ group $; datalines; 1 1 A AB 1 2 B AB 2 1 B BA 2 2 A BA ; run; proc sql; select patient, time,drug, cat('patient',patient, ' belongs to group ', '"',compress(group),'"') as group from want; quit; data want; input patient time drug $ group $; datalines; 1 1 A ABB 1 2 B ABB 1 3 B ABB 2 1 B BA 2 2 A BA ; run; proc sql; select patient, time,drug, cat('patient',patient, ' belongs to group ', '"',compress(group),'"') as group from want; quit; ... View more

Hi @Anv_SAS , I think this is what you wanted to achieve by the code you provided, however, you did not get what you desired. Here is my suggestion, please kindly let me know if it answer your question.   (1) you applied categories to the age and sex variables through the proc sort and merge steps, the code and output are as follows (in the output table, column n is age category and column m is sex category): proc sort data=sashelp.class out=class0 nodupkey; by age; run; data class0; set class0; n=_n_; run; proc print data=class0;title "class0";run; proc sort data=sashelp.class out=class; by age; run; proc print data=class;title "class";run; data class2; merge class(in=a) class0(in=b keep=age n); by age; if a; if sex="F" then m=1; if sex="M" then m=2; run; proc print data=class2;title "class2";run;   (2) you were going to produce a macro variable which is the combination of the age and sex categories, and this macro variable resolves to respective students' names in the class as its' macro variable values. However, you did not choose the simple and efficient method to do this (you applied many techniques including macro, sql, nested do loop, which made the code difficult to debug). Here is my thinking and suggestion: you can simply use a single sql or data _null_  step to produce this macro variable. proc sql; select name,n,m into :name1-,:n1-,:m1- from class2; quit; %put _user_; data _null_; set class2; call symputx(name,cat(n,m)); run; %put _user_; data _null_; set class2; call symputx(cat('age',n,m),name); run; %put _user_; 86 %put _user_; GLOBAL AGE11 Joyce GLOBAL AGE12 Thomas GLOBAL AGE21 Louise GLOBAL AGE22 Robert GLOBAL AGE31 Barbara GLOBAL AGE32 Jeffrey GLOBAL AGE41 Judy GLOBAL AGE42 Henry GLOBAL AGE51 Mary GLOBAL AGE52 William GLOBAL AGE62 Philip GLOBAL ALFRED 42 GLOBAL ALICE 31 GLOBAL BARBARA 31 GLOBAL CAROL 41 GLOBAL HENRY 42 GLOBAL JAMES 22 GLOBAL JANE 21 GLOBAL JANET 51 GLOBAL JEFFREY 32 GLOBAL JOHN 22 GLOBAL JOYCE 11 GLOBAL JUDY 41 GLOBAL LOUISE 21 GLOBAL M1 1 GLOBAL M10 2 GLOBAL M11 2 GLOBAL M12 1 GLOBAL M13 2 GLOBAL M14 1 GLOBAL M15 1 GLOBAL M16 1 GLOBAL M17 2 GLOBAL M18 2 GLOBAL M19 2 GLOBAL M2 2 GLOBAL M3 2 GLOBAL M4 1 GLOBAL M5 2 GLOBAL M6 1 GLOBAL M7 2 GLOBAL M8 1 GLOBAL M9 1 GLOBAL MARY 51 GLOBAL N1 1 GLOBAL N10 3 GLOBAL N11 4 GLOBAL N12 4 GLOBAL N13 4 GLOBAL N14 4 GLOBAL N15 5 GLOBAL N16 5 GLOBAL N17 5 GLOBAL N18 5 GLOBAL N19 6 GLOBAL N2 1 GLOBAL N3 2 GLOBAL N4 2 GLOBAL N5 2 GLOBAL N6 2 GLOBAL N7 2 GLOBAL N8 3 GLOBAL N9 3 GLOBAL NAME1 Joyce GLOBAL NAME10 Jeffrey GLOBAL NAME11 Alfred GLOBAL NAME12 Carol GLOBAL NAME13 Henry GLOBAL NAME14 Judy GLOBAL NAME15 Janet GLOBAL NAME16 Mary GLOBAL NAME17 Ronald GLOBAL NAME18 William GLOBAL NAME19 Philip GLOBAL NAME2 Thomas GLOBAL NAME3 James GLOBAL NAME4 Jane GLOBAL NAME5 John GLOBAL NAME6 Louise GLOBAL NAME7 Robert GLOBAL NAME8 Alice GLOBAL NAME9 Barbara GLOBAL PHILIP 62 GLOBAL ROBERT 22 GLOBAL RONALD 52   (3) to conclude, the last data _null_ step might be the solution, which produces desired result and the macro variable &age11 was correctly resolved to a class student's name, Joyce, the code and results is as follows. data _null_; set class2; call symputx(cat('age',n,m),name); run; %put &age11; 69 data _null_; 70 set class2; 71 call symputx(cat('age',n,m),name); 72 run; NOTE: There were 19 observations read from the data set WORK.CLASS2. 73 %put &age11; Joyce   ... View more

Thanks a lot for reply, @Tom ! So this is what I understand now: (1) The if _n_=1 then do; statement is ALWAYS necessary in hash method, i.e., the basic syntax of hash method could be the follows (the scenario is I have two datasets, dsn1 and dsn2, I would like to combine them base on a key column, column1) , am I right or wrong? Another question is that, for the rc=h.find(key: column1); statement, I see there are examples such as rc=h.find(); , i.e., there is no key column specified inside the brackets, my question is: when do I need to specify the key:column in this statement? data combine; if _n_=1 then do; declare hash h(dataset: 'dsn1'); h.definekey('column1'); h.definedata('column2','column3'); h.definedone(); end; set dsn2; rc=h.find(key: column1); if rc=0; run; (2) The if 0 then set; is NOT always necessary, but in a lot of cases I need to write this statement. And it can be replaced by call missing (column1,column2,column3); statement after the h.definedone(); statement, am I right? (3) When writing hash codes and do NOT write if 0 then set; I sometimes get error message such as: (a) ERROR: Undeclared data symbol columnxxx for hash object, and (b) ERROR: Variable columnxxx has been defined as both character and numeric. Does this mean, always add the if 0 then set; statement in my hash code if I do not know whether I need to write it? ... View more

Hi this is the answer base on others' reply and codes: data class; input Name$ Sex$ Age Height Weight; datalines; Alfred M 14 69.0 112.5 Alice F 13 56.5 84.0 Barbara F 13 65.3 98.0 Carol F 14 62.8 102.5 Henry M 14 63.5 102.5 ; run; data cities; input zipcode City :$30. Department :$30. Persona:$10.; infile datalines dlm=',' dsd; datalines; 75008, Paris 8è ,Paris,Alice 91940, Les Ulis, Essonne,Carol 92330,Sceaux, Hauts-de-Seine,Henry 93140, Bondy , Seine-Saint-Denis,Henry 94150, Rungis, Val-de-Marne,Alice ; run; proc print data=class;run; proc print data=cities;run; data set1; if _n_=1 then do; if 0 then set class(keep=name sex age); declare hash match1(dataset:'class'); match1.definekey('name'); match1.definedata('sex','age'); match1.definedone(); end; set cities(keep=persona department); name=persona; rc=match1.find(key:name); if rc=0; drop persona rc; run; proc print data=set1;run;   ... View more